Duration help required

[frustrated]

Hi

I would like to calculate the duration of an event between 06:00 yesterday and 06:00 today. I know what date and time the event started and finished and have this information separated into columns (as per the attached sheet).

I have used the following formula to get overall duration for the event:

=SUM((end date+end time)-(start date+start time))*24

My problem is that I would like to ignore any duration before 06:00 yesterday and after 06:00 today so I am only considering the time between the specified period. Based upon this the max duration I can have is 24 hours.

Can someone help me with this as this is driving me crazy and confusing my brain!

Thanks in advance

Duration.xlsx

[Ace_XL]

Maybe..

=MAX(0,(MIN(K4+L4,TODAY()+TIME(6,0,0))-MAX(J4+I4,TODAY()-1+TIME(6,0,0)))*24)

[frustrated]

Hi Ace_XL

Thanks for your suggestion. Unfortunately the formula does not return the value required.

Thanks for trying.