Time rounding and decimalisation formulaes

[Lewis_Raft]

Hello MrExcel Patrons,

I have to create a sheet whereby i can enter the values for staff starting times, but they will be exact, for example.

8:23, this figure would need to be rounded to the nearest 15 minute, whether that be up or down.

Furthermore i need to calculate from these times the following information.

The amount of time spent during "Overtime" hours, i.e. time before 9 and time after 6, and then to multiply this information by the quarterly pay or hourly pay figure.

So in an ideal world, i would enter into A2 (Start Time) 5:23. B2 (Finish Time) 7.43. then have a formula in a set of cells that would automatically calculate. the rounding of those times to nearest 15 minute interval. to calculate how many of those hours fall outside standard pay times, and to multiply those by the overtime pay level, also to calculate how much of that time falls within standard pay levels and multiply that by the respective standard pay amount, to have those 2 figures combined.

Ive tried a few different rounding functions to no avail, im using Excel 2003.

Furthermore, on a separate note, in a different spreadsheet ive exceeded the 44million limit in calculations and wondered if anyone would know a quick and easy way to shorten this code:-

=SUM(IF(COUNTIF('[2014 Rota.xls]February'!$C$5,"Sales"),((([Robsight.xls]February!$B$5)/1.2)/100)*Standards!B8,0),(IF(COUNTIF('[2014 Rota.xls]February'!$C$5,"St Albans"),((([Robsight.xls]February!$D$5)/1.2)/100)*Standards!B20,0)),(IF(COUNTIF('[2014 Rota.xls]February'!$C$5,"TCR 2")+COUNTIF('[2014 Rota.xls]February'!$C$5,"TCR 1"),((([Robsight.xls]February!$F$5)/1.2)/100)*Standards!B12,0)),(IF(COUNTIF('[2014 Rota.xls]February'!$C$5,"Angel"),((([Robsight.xls]February!$H$5)/1.2)/100)*Standards!B16,0)),(IF(COUNTIF(Standards!B26,"X"),((([Robsight.xls]February!$J$4)/1.2)/100)*Standards!B24,0)))

Without losing any functionality.

I have been wracking my brains over the prior question for some time and i cant figure a way around it, im not sure if im using the correct cell format to achieve the desired goal but im hoping you magic men and women can help me.

Kind Regards,
Lewis

 

[Rick Rothstein]

Just wondering why don't you subtract the real times and then round that to the nearest 15 minutes? Anyway, you can use this formula to do the rounding...

=MROUND(A1,TIME(0,15,0))

Obviously, you would replace the A1 by a reference to the cell containing the time value you wanted to round. Note that this formula will probably give you the time serial number (a floating point value)... simply format the cell as Time to see it in human readable form.

 

[Lewis_Raft]

Hi,

Thanks for the reply, i dont know if im just being incredibly stupid or not but it seems that i cant get this simple formula to work.

What i want to do is in put into a column, A1 for example, the time that someone started, input into B1, the time that someone finished. and from these inputs have a cell automatically calculate the due pay of that individual, assuming that there are different rates for before 9 and after 6. I dont really care how its calculated, aslong as i can change at will the rates involved and the time brackets.

So any method of calculating this would be great.

Cheers!

 

[steve1033]

Hi there I'm new here and maybe this is a daft question but i need something that is I think a basic function but cant work it out i need to enter real time into a time sheet then work the hours out into units of 100 or decimal units if you like ie. 7 till seven will be 12 hours but 7 till 7:30 will be 12.5 hours not 12 hours 30 minutes .

 

[Rick Rothstein]

Hi there I'm new here and maybe this is a daft question but i need something that is I think a basic function but cant work it out i need to enter real time into a time sheet then work the hours out into units of 100 or decimal units if you like ie. 7 till seven will be 12 hours but 7 till 7:30 will be 12.5 hours not 12 hours 30 minutes .

Time, to Excel, is a decimal value representing the fraction of a 24-hour day (what it shows you is only for human convenience), so if you multiply the difference by 24 (you will probably also have to change the Cell Format to General after you enter the formula into the cell), it will give you the hours as a floating point number. So, if your start time is in cell A1 and your end time is in cell B1, then use this formula...

=24*(B1-A1)

and format the cell you put the formula in as General.

NOTE: For future questions you may have, it is better to start a new thread rather than jump on the end of an existing thread... to the volunteers who answer question in this forum, you message will look like a follow up to other comments, so if they were not participants in the original thread, they will not look at your question because they would have no way of knowing it was a new question.

 

[steve1033]

thank you very much