Calculating hours from time

Kamya Pamnani

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.

SKR

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!

SKR

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!

SKR

Kamya Pamnani

Hi SKR,

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

Thanks,

Kamya.

Kamya Pamnani

Kamya Pamnani

Hi SKR,

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

Thanks,

Kamya.

SKR

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))))