CODEWITHSUNDEEP
timeexcel-formulaintervals
Peter Levinsson
Peter Levinsson

Reputation: 13

Time within a time interval

I found this formula here in the forum and it works perfectly when the time shift ends after midnight. I tried the same formula with ending time 23:00 but I can't get it to work. What am I doing wrong?

I want it to count hours between 18:00–23:00.

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)

Upvotes: 1

Views: 135

Answers (1)

E. Villiger
E. Villiger

Reputation: 916

Try this:

=MEDIAN((C5+(C5*24 < B5*24))*24,18,23)-MEDIAN(B5*24,18,23)

B5 is start date, C5 is end date.

Upvotes: 1

Related Questions