Round total working hours

[Fayyfatt]

Hi,

i wanted to calculate overtime working hours and would like to include below:
1) round total hours to every 0.25
2) weekday >>>> minus 0.5 hr for =>3hrs and minus 1.0 hra for =>6hrs
3) weekend >>>> minus 0.5 hr for =>6hrs and minus 1.0 hra for =>8hrs

issue example :
if i use END TIME (11.59 PM) - START TIME (2.00 PM)= 9.59
I could not round it to 0.25

if i use MOD((11.59 PM - 2.00),1)*24 PM = 23.36

 

[Chrisdontm]

Some ideas....

in working with 23.36 if it is in cell K25


=MROUND(K25,0.25) → 23.25

=ROUNDUP(K25/0.25,0)*0.25 → 23.5

=ROUNDDOWN(K25/0.25,0)*0.25 → 23.25

 

[Fayyfatt]

thank you Chris. But did i use the correct formula to calculate working hours between time in and time out.
if i use END TIME (11.59 PM) - START TIME (2.00 PM)= 9.59
I could not round it to 0.25

if i use MOD((11.59 PM - 2.00),1)*24 PM = 23.36
i could round it but why the result 23.36 and not 23.59 (before round)

 

[Chrisdontm]

When you look to find the difference of hours in time, you are actually looking for a "number" of hours, not a "time" result.
So the cell with the formula should be formatted as a "Number".
And since you want to see the quarter hours results, format it with two decimal places.
Taking 11:59 PM - 2:00 PM; that is 9.98 hours, ( as the number of actual hours ).

Lets say that your start time is in cell: " D12 "
And your end time is in cell: " E12 "

So to get the actual hours;
=IF(D12="","",IF(E12="","",IF(MOD(E12-D12,1)*24=0,"",MOD(E12-D12,1)*24)))
That will give you: 9.98

Then if you wanted to try the three variations mentioned above, here is how they would work.

=MROUND(IF(D12="","",IF(E12="","",IF(MOD(E12-D12,1)*24=0,"",MOD(E12-D12,1)*24))),0.25)
That will give you: 10.00

=ROUNDUP(IF(D12="","",IF(E12="","",IF(MOD(E12-D12,1)*24=0,"",MOD(E12-D12,1)*24)))/0.25,0)*0.25
That will give you: 10.00

=ROUNDDOWN(IF(D12="","",IF(E12="","",IF(MOD(E12-D12,1)*24=0,"",MOD(E12-D12,1)*24)))/0.25,0)*0.25
That will give you: 9.75

Try different time entries and see which way you want it to go.
I hope this helps...

 

[Fayyfatt]

tq very much.. this is very helpful