Calculating hours from time
Hi, I need to calculation the number of hours from the Start time as well as End time and deducting 30 mins lunch break. Example: If start time is 10am and end time is 6pm which is basically 8 hours then deduct the lunch break and the final output is 7.5. Please help on how to apply the formula.
Thanks.
Best Answer

Hi Kamya,
Based on a couple of assumptions:
 There is no overnight scenarios
 " . " must be keyed in as " : "
With that this formula works: =24 * ((TIME([End Time]1, 1, 2)  30)  TIME([Start Time]1, 1, 2))
Oh by the way, this formula will deduct 30mins (24* ((Ti..... 30) even if it is less than 7 hours. So you need to tweek for other requirements.
Hope this helps!
Answers

Hi Kamya,
Based on a couple of assumptions:
 There is no overnight scenarios
 " . " must be keyed in as " : "
With that this formula works: =24 * ((TIME([End Time]1, 1, 2)  30)  TIME([Start Time]1, 1, 2))
Oh by the way, this formula will deduct 30mins (24* ((Ti..... 30) even if it is less than 7 hours. So you need to tweek for other requirements.
Hope this helps!


Hi SKR,
Thank you so much. It worked and got the desired results.
Thanks,
Kamya.

Hi SKR,
If the time is less than 7 hours example 4 hours then what tweek should be done. Please help.
Thanks,
Kamya.

You can try this:
=IF((TIME([Start Time]1, 1, 2) + 240) >= TIME([End Time]1, 1, 2), (24 * ((TIME([End Time]1, 1, 2))  TIME([Start Time]1, 1, 2))), (24 * ((TIME([End Time]1, 1, 2)  30)  TIME([Start Time]1, 1, 2))))
Help Article Resources
Categories
Check out the Formula Handbook template!