Forum Discussion
Creating Excel time sheet with 1/4 hours
Good day, I am trying to create a time sheet in excel In - Lunch out - Lunch in - Out. Currently, I have the formula in pieces:
In - Lunch Out
Lunch In - Out
But I cannot get it to add the total time across the day, I have it broken into two sections. I also cannot get a total of hours at the end of the week. I have to total each section at the bottom and then total the two totals. Lastly, I would like to see the time convert to 1/4 hours .25 vs .15 or .50 vs .30 . Any assistance would be greatly appreciated.
2 Replies
the total per day as time (for example 7:15) is
=MROUND(MOD(Lunch Out - In, 1)+MOD(Out - Lunch In, 1), TIME(0, 15, 0))
If you want it in decimal hours (7.25), use
=MROUND(24*(MOD(Lunch Out - In, 1)+MOD(Out - Lunch In, 1)), 0.25)
and format the cell with the formula as General or as Number.
I have no idea what any of that meant, but I copied your formula and it worked perfectly. Thank you so much for your guidance. It is much appreciated!
