Subtracting Dates - Mike

[Guest]

Hi,

I'm trying to figure out the elapsed time (or the turnaround time) between
two dates at work. I have input it as such:

Date Received: 5/4/07 3:41 PM
Date Due By: 5/8/07 1:40 PM

Using this formula: =(DAY(G5)-DAY(F5))&" days and "&TEXT(G5-F5,"h:mm")

For that specific time frame, the formula works perfectly. I have this for
an answer: 4 days and 21:59

However, when these two dates come up, I have a problem.

Date Received: 4/30/07 4:26 PM
Date Due By: 5/3/07 1:00 PM

For that date range, I bring this back for an answer: -27 days and 20:34

I know, in theory, why it is pulling back that answer, but does anybody know
what adjustments I could make to the formula to bring me back the answer I'm
looking for? Thanks a lot.

 

[JE McGimpsey]

One way:

=INT(G5-F5) & " days and " & TEXT(MOD(G5-F5,1), "h:mm")


XL stores date/times as offsets from a base date, with days as integers
and times as fractions.

 

[Guest]

=DATEDIF(F5,G5,"y")&" years, "&DATEDIF(F5,G5,"ym")&" months,
"&DATEDIF(F5,G5,"md")&" days "&TEXT(MOD(G5-F5,1),"hh "" hours "" mm"" minutes
"" ss ""seconds""")

will return:

0 years, 0 months, 3 days 20 hours 34 minutes 00 seconds

 

[JE McGimpsey]

Yes, but try it with

F5: 01/31/2007 15:20
G5: 03/01/2007 14:10

0 years, 1 months, -2 days 22 hours 50 minutes 00 seconds

Probably not what the OP wanted...

Better just to do the math.

 

[Guest]

Thank you for your first post. It seems to be working the way I wanted it to.

-Mike