=FormatNumber(Floor([Number of Seconds]/3600);"00 :") +
FormatNumber(Floor(Mod([Number of Seconds];3600)/60);"00 :") +
FormatNumber(Mod(Mod([Number of Seconds];3600);60);"00")
The value it is returning as below:
-24:-35:00
-24:-50:00
-24:-41:00
The negative values persist in the “average” variable when the call starts before midnight and ends the next day.
Is there any work around, please help:
I thought to do as below:
1)Dispatched Time-Minute
=FormatDate([Dispatched Time];"mm")
2)On Location Time in Minute
=FormatDate([On Location Time];"mm")
But don’t know how to get the difference because I am getting an error while using a daysbetween formula…
What you should probably do is, effectively, change both datetime values to epoch values before you do the subtraction, then change them back. Anything else requires you to check if the day has changed so that your hours aren’t negative, if the hour changed so your minutes aren’t negative, and so on.
You’ve already got the basic formula down. Days times 86400 plus hours time 3600 plus etc. Do this calculation for each time separately (I would even make them separate variables for extra clarity). But instead of calculating the days between your start times, use a common starting date (say 1/1/2014 if your data doesn’t go too far back, or 1/1/1970 if you really like actual epoch times).
Subtract your dispatch time from the on location time to get the number of seconds between.
Now you always have a positive number (unless some wacky guy shows up before he’s dispatched). Average this number.
Now that you have an average number, convert it back to a hh:mm:ss format by reversing the process, using the code you already have.
Well, I did say you’d have to monkey with it. My first guess of a problem would be declaring the constant for 1/1/2014. That’s probably the wrong format, or you have to use ToDate, or something like that.