IF statement

  • Thread starter Thread starter Fay Yocum
  • Start date Start date
F

Fay Yocum

Is there a way to use an IF statement to do the following:

Start time End time Total Hr Nights Days Evenings
0700 1900 12 0 8 4
1900 0700 12 8 4

Thank you

Fay
 
Hi

You can calculate hours for case with midnight rollover by formula
=EndTime-StartTime+(EndTime<StartTime)
(NB! The formula works when time interval is less than 24 hours)

The formulas to calculate night, day or evening time must include according
start/end times, and will be quite complex.
P.e. yo have times DayStart and DayEnd. Generally daytime will be calculated
as (It's on fly, check the formulas yourself):
DayTime=DayTime1+DayTime2
DayTime1=AND(StartTime<DayEnd,OR(EndTime>DayStart,EndTime<StartTime))*(MAX(StartTime,DayStart)-MIN(EndTime+(EndTime<StartTime),DayEnd))
DayTime2=AND(EndTime<StartTime,EndTime>DayStart)*(MIN(EndTime,DayEnd)-DayStart)
As follows
DayTime=AND(StartTime<DayEnd,OR(EndTime>DayStart,EndTime<StartTime))*(MAX(StartTime,DayStart)-MIN(EndTime+(EndTime<StartTime),DayEnd))+AND(EndTime<StartTime,EndTime>DayStart)*(MIN(EndTime,DayEnd)-DayStart)

etc.
 
Back
Top