I have 2 times that may cross midnight. Is there a way to create a formula that if it passes 2400 hrs that it can make the necessary calculation. Or can the calculation be made on a date/time stamp for each column?
It would never be more than one day.
Thanks.
Hopefully, you’ve already figured this out.
Here’s a solution, if not:
MOD((LEFT({Stop Time},2)*60+IF(RIGHT({Stop Time},2)=“PM”,720,0)+(MID({Stop Time},4,2)))-(LEFT({Start Time},2)*60+IF(RIGHT({Start Time},2)=“PM”,720,0)+(MID({Start Time},4,2)))+1440,1440)/60
The calculations are done in minutes, then divided by 60 at the end to give the result in hours. 1440 is the number of minutes in 24 hours. The MOD function removes the potential excess 1440 minutes. I did it this way to avoid additional IF functions. “LEFT({Stop Time},2)*60+IF(RIGHT({Stop Time},2)=“PM”,720,0)+(MID({Stop Time},4,2)))” converts the hours to minutes, adds 720 minutes (12 hours) if it is PM
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.