Calculating response times from timestamp logs that cross midnight

Appel Malade

New member
When a log only stores clock times without the date, any call raised before midnight and answered after it gives a negative duration, and Excel displays a row of hash signs instead of the delay. The simplest fix is to wrap the subtraction in MOD, with the end time minus the start time as the first argument and one as the divisor: since Excel stores a full day as one, MOD returns the correct positive fraction of a day. Format the result cell as [h]:mm:ss so that long delays are not cut back to the clock value. If the export contains full date-time stamps, prefer a plain subtraction of the two stamps, because MOD would silently hide a gap longer than twenty-four hours. To get the delay in minutes for averages or thresholds, multiply the result by the number of minutes in a day; a frequent mistake is to use MINUTE(), which only returns the minute part of the time. Also check that the exported stamps are real times and not text: convert them with TIMEVALUE or DATEVALUE first, otherwise the subtraction returns #VALUE!.
 
Hello Appel Malade,

Thank you for sharing this detailed and helpful explanation. You're absolutely right. The MOD function is a simple and effective way to calculate elapsed time when a call starts before midnight and ends after midnight.

For example, if the start time is in A2 and the end time is in B2, we can use:
=MOD(B2-A2,1)

To calculate the delay in minutes:
=MOD(B2-A2,1)*1440

As you mentioned, this approach works best when the elapsed time is less than 24 hours. For records containing complete date-time values, direct subtraction (=B2-A2) is more reliable because it preserves durations longer than one day.

Your reminder about using [h]:mm:ss formatting and checking for text-formatted timestamps is also valuable.

Thanks again for contributing these practical tips to the community!
 

Online statistics

Members online
0
Guests online
218
Total visitors
218

Forum statistics

Threads
468
Messages
2,092
Members
6,827
Latest member
vetrosaunarp
Back
Top