MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Time difference calculations across midnight

  Asked By: Anita    Date: Feb 18    Category: MS Office    Views: 1846

In the program I working on for handling call times, a situation
came up which turned up an error so I would like any suggestions you
have for this. It seems like something like this came up here in
discussion recently, but when I looked, I couldn't find it my files
of previous postings. If this is a repeat, my apologies, folks.
Since it's a 24-hour operation, a call started at 23:58:58 and
handling the call went past midnight, so the dispatch time was
listed as 00:00:23 (i.e. 23 seconds past midnight). So, as per my
own code which looks for confusion in the data, it got kicked onto
the Error List sheet, because it was calculated as a negative
timelapse (which is, of course, impossible). At this point, the
program is being beta tested so the supervisor and I talked about
this, so it will be hand corrected this particular month.
Of course, I'd like to get it corrected as soon as I can, so it
doesn't repeat. Any suggestions? If anyone needs to see the code I
have, I'll be glad to post it, but I thought the problem was already
pretty clear.



7 Answers Found

Answer #1    Answered By: Grady Stewart     Answered On: Feb 18

If the time  is a normal date/time value it will not matter. The datetime
value is a decimal number of days since some long distant date that is it as
two parts and integer part for the day and a decimal part for the fraction
of a day.

If you use this variable you can subtract the start time from the end time
without problems. ten past  midnight will have a higher integer value than
ten to midnight the day before. You only need to check that the call  was
not completed before it started  by checking end<start.

If you have reinvented the way to track time I suggest you rethink.

Answer #2    Answered By: Brendan Smith     Answered On: Feb 18

You should really provide more information. I.e. how are you entering these
times? Are they in cells as times (i.e. with colons)? I'll assume so.

Therefore your elapsed time  calculation will be something like


Seeing there is no date information in the date-time fields, you need to
compensate. Basically, you need the result modulo one day. I.e.


If you're holding the times in some other fashion, you'll need to go modulo
24 hours or one day, as appropriate.

Answer #3    Answered By: Faiza Mian     Answered On: Feb 18

I do see now that I left out important
information, but you solved it anyway. Your assumption that the
times are in cells as times with colons was correct, and I had
actually overlooked that I had stripped the date information out
intentionally because the data  comes in 2 different forms from 2
different sources. Separating the DATE and time  was a way to make
sure the data was in the same form for analysis.
So, David G.'s point about the original Excel values for dates
and times would have worked, except that I had removed the DATE
part, which I should have mentioned. However, the MOD part of the
formula that David S. directed me to seems to have solved the
problem. I've got to sift through the existing formula in order to
add that in, because I had written in error  traps in the formula
itself, but that shouldn't be hard to figure out.
Thanks again to a great group. You did it again.

Answer #4    Answered By: Felix Gray     Answered On: Feb 18

I can add that if you include the dates, the subtraction will work; i.e.
subtract the START date and time  from the END date and time.

Use the function:

= ( EndDate + End Time ) - ( StartDate + StartTime )

Answer #5    Answered By: Sultana Tabassum     Answered On: Feb 18

the DATE has been stripped from this part of the data, because one
of the 2 data  sources does NOT include a DATE--therefore in order
for the data to be consistent, I removed the the date from all of
these entries. Otherwise, your comment is a welcome one, thank you.

(I wonder is that would make it a "bad data day" instead of a "bad
hair day"? . . . heheh. . . . just kidding.)

Answer #6    Answered By: Hollie Hughes     Answered On: Feb 18

If you have 23:58:58 in cell A1 and 00:00:23 in cell B1 enter the following
formula in C1:

Will this work for you?

Answer #7    Answered By: Jackson Williams     Answered On: Feb 18

Again thanks for your thoughts, and I am considering the
possibilities of incorporating each of these ideas from all who have
responded. The latest problem  I discovered is that some of the 'bad
data' errors include a null value (like when the system cannot
interpret the input) in the source, which get's outputed
as "00:00:00" for the dispatch value--which get's interpreted as
midnight, exactly.
So, using your formula would interpret a 'null' entry, which I
had hoped to kick to the error  List, as a legit entry, so it would
undo the previous  error traps.

With all that everyone has offered, though, my latest
attempt/thought has been to have a there supervisor make a judgement
on the time  of the dispatch. If they listen to the call  (which are
all recorded, since this is a 911 center) and find  that the call's
dispatch time is legit and in fact it was dispatched exactly at
midnight "00:00:00", or a few seconds  later, then with a button,
that time will be "honored" in the calculations. Otherwise, I can't
see anyway to filter the calculations, without undoing the whole
purpose of the error check.

One thing for sure, though, all the ideas which everyone can
kick out will be welcome to me, indeed, with thanks. I've said it
many times, but this is a great group and I read the postings every

Didn't find what you were looking for? Find more on Time difference calculations across midnight Or get search suggestion and latest updates.