CODEWITHSUNDEEP
excel
Rails beginner
Rails beginner

Reputation: 14504

Excel how get number of hours in a time interval?

I have 2 columns with:

Night shift start: 19:00

Night end: 04:00

And I have some date columns with for each day.. 

Work started: 07:30

Worked ended: 22:00

I want to get the number of hours as a decimal that is between the night shift start and night end. I need to calculate the number of "night shift hours" for worked hours.

From comment: I do not want to get the total number of hours. I want to calculate the number of "night shift hours" and that is hours between 19:00-04:00

Upvotes: 2

Views: 5853

Answers (3)

barry houdini
barry houdini

Reputation: 46331

Given start time in B5 and end time in C5 this formula will give you the decimal number of hours that fall in the range 19:00 to 04:00

=MOD(C5-B5,1)*24-(C5<B5)*(19-4)-MEDIAN(C5*24,4,19)+MEDIAN(B5*24,4,19)

format result cell as number

Upvotes: 1

PA.
PA.

Reputation: 29339

just substract the two dates to get the difference in days, and multiply by 24 to get the difference in hours

=(B1-A1)*24

this is correct when both B1 and A1 contain a datetime value, but in case your cells contain just a time value, with no day value, and given that the calculation spans the night (there is a day change in between) you need to add one day to the difference:

=IF(B1<A1,1+B1-A1,B1-A1)*24

Upvotes: -1

shahkalpesh
shahkalpesh

Reputation: 33476

=IF(B1-A1 < 0, 1-(A1-B1),( B1-A1))

Assuming that cell A1 contains start, B1 contains end time.
Let me know, if it helps OR errors.

Time without date is not enough to do the subtraction considering the start can be the night before today.

Are you OK to try VBA?

EDIT: The formula is meaningful within 12 hour limit. I will see if it can be made simpler.

Upvotes: 1

Related Questions