Forum Discussion

SherryBlackburn's avatar
SherryBlackburn
Qrew Trainee
7 years ago

Date/Time duration formula in hours, excluding 12am-4am

I am calculating elevator/escalator outages and need a formula that will calculate the hours between two Date/Time fields "Incident Start Date/Time" and "Incident End Date/Time".  The kicker is I need...
Formulas and functions
EvanMartinez's avatar
EvanMartinez
Moderator
7 years ago
Hi Sherry,

Would it ever be the case that an outage would be resolved in that 12-4 AM gap you don't want to count or would it be that they wouldn't be resolved in that time frame? Would they go for longer then 1 day potentially? Say 3 days so those hours would need to be excluded for 12-4 AM multiple times?

SherryBlackburn's avatar
SherryBlackburn
Qrew Trainee
7 years ago
The reason for the exclusion is that the equipment is not in service during those hours.  So we want those hours removed from the calculation, so that the outage hours only reflect the hours when the equipment should be operational for service, but is not.

It is highly unlikely, but not impossible, that an outage would be resolved during those hours.  An outage could last more than one day, so those hours would need to be excluded for every 24 hours.