I am trying to work out the total hours worked over certain period which includes night shifts. The sum of the total hours works well if the finish time is within the same day but not for the days where the finish times go beyond 24:00 hours.
Can you please help me find where the formula or formatting is incorrect. Example attached for reference.
Many Thanks
Hi there,
I might not have the most elegant solution, but I would suggest fixing the formulas that include the *24 multiplication as it confuses the excel time calculation. I am attaching my suggestion. It uses an if statement so that if the second time is smaller than the first it simply adds 24 hours (and if the second time is greater than the first then it ignores that need and just does the subtraction).
Making it work is a little messy in my formulas so someone might have a better solution. I put 24:00:00 into cell D1 and reference that to add the 24 hours.
Of course you could hide that figure so no one ever sees it.
Example attached
Hi Varma
Just remove the *24
For example =B3-A3+(B3<A3)
It is better for the time to be combined with a date, then it will be easier to calculate e.g. 25/07/2019 07:30 AM instead of 07:30 AM
You can refer to this https://www.myonlinetraininghub.com/excel-date-and-time to learn more about date and time in Excel.
Hope this helps.
Sunny
