# Calculating hours from time

Options
✭✭✭✭

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.

• ✭✭
Options

Hi Kamya,

Based on a couple of assumptions:

1. There is no overnight scenarios
2. " . " 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!

• ✭✭
Options

Hi Kamya,

Based on a couple of assumptions:

1. There is no overnight scenarios
2. " . " 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!

• ✭✭
edited 10/26/23
Options
• ✭✭✭✭
Options

Hi SKR,

Thank you so much. It worked and got the desired results.

Thanks,

Kamya.

• ✭✭✭✭
Options
• ✭✭✭✭
Options

Hi SKR,

If the time is less than 7 hours example 4 hours then what tweek should be done. Please help.

Thanks,

Kamya.

• ✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!