Calculate hours from start time and end time

Question

Hi

Lets say, that I have work shifts in excel sheet, where

Cell A1 = Start time = 11AM
Cell B1 = End time = 8PM

These cell's are formatted as TIME. Total hours here is 9 hours.

Is it possible somehow to check, how many hours there is between

From 9AM to 12PM
From 12PM to 3PM
From 3PM to 6PM
From 6PM to 9PM

?

Result shoud be
From 9AM to 12AM = 1 hour
From 12AM to 3PM = 3 hours
From 3PM to 6PM = 3 hours
From 6PM to 9PM = 2 hours

Answer 1

Hi @IMK

Thanks for your explanation.

In addition to the formula you shared "=MAX(0, MIN(B1, TIME(20,0,0)) - MAX(A1, TIME(11,0,0)))", you may also try the formula like =HOUR(MIN(B$1,B2)-MAX(A$1,A2)).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Times are stored internally as a fractional part of a day. You can see this if you change the display format to General.

When you subtract one time from another, the result is still a fractional part of the day. If you want to use this value in further computations where it makes more sense to use 8 hours rather than .3333 days, simply multiply the value by 24 and display it with the appropriate Number format, not a time format.

For example, subtracting 9:00 AM from1:30 PM produces .1875 and after multiplication yields 4.5.

Answer 3

Your result display doesn't line up. The # of hours between 9AM and 12AM = 3 + 12 = 15 hours, not 1 hour. 12 AM - 3 PM = 12 + 3 = 15 hours, not 3 hours. 6 PM to 9 PM = 3 hours, not 2 hours.

The formula for getting the # of hours between 2 times is simply =HOUR(A1:B1). Place that into column C1 and then drag and drop the formula to remaining rows to get the calculated hours. Note that you should format the cells as General to get just the hours.

Answer 4

My apologies..

I mistyped the time. I meant noon, meaninig 12PM. So the correct results would be

From 9AM to 12PM = 1 hour
From 12PM to 3PM = 3 hours
From 3PM to 6PM = 3 hours
From 6PM to 9PM = 2 hours

I got this sorted out already. I used

MAX(0, MIN(B1, TIME(18,0,0)) - MAX(A1, TIME(6,0,0)))