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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!