Round total working hours

Fayyfatt

New Member
Joined
Jan 3, 2019
Messages
7
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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)
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top