Date Calculations as Days

F

FrankM

I would like the end result to include Days, Hours, Minutes and Seconds.

In one cell I have the first date: 7/19/2006 2:54:51 PM
In a second cell I have the end date: 10/5/2006 2:12:06 PM

I'm trying to calculate the difference but I don't want Months, Months
should be counted as Days. I tried the following formula and I thought it was
going to work but it doesn't ...

=DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & "
Minutes " & SECOND(D8-C8) & " Seconds "

The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it
should be 77 Days not 78 Days. The number of Days is 78 if you don't take
into account the time but because of the time it is not technically 78 Days
yet.

Any thoughts?

Please! Help! Please!
 
R

Ron Rosenfeld

I would like the end result to include Days, Hours, Minutes and Seconds.

In one cell I have the first date: 7/19/2006 2:54:51 PM
In a second cell I have the end date: 10/5/2006 2:12:06 PM

I'm trying to calculate the difference but I don't want Months, Months
should be counted as Days. I tried the following formula and I thought it was
going to work but it doesn't ...

=DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & "
Minutes " & SECOND(D8-C8) & " Seconds "

The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it
should be 77 Days not 78 Days. The number of Days is 78 if you don't take
into account the time but because of the time it is not technically 78 Days
yet.

Any thoughts?

Please! Help! Please!


A1: start date/time
A2: end date/time

=INT(A2-A1)&" days, " & TEXT(MOD(A2-A1,1),"h"" hrs ""mm"" min ""ss"" sec""")


--ron
 
J

Jim Thomlinson

Check the time to see if you need to subtract a day or not... Something like
this...

=DATEDIF(C8,D8,"d")-IF(MOD(C8,1)>MOD(D8,1),1,0)&" Days "&HOUR(D8-C8)&" Hours
"&MINUTE(D8-C8)&" Minutes "&SECOND(D8-C8)&" Seconds "
 
F

FrankM

AWESOME. It worked perfectly! Thank you.

Ron Rosenfeld said:
A1: start date/time
A2: end date/time

=INT(A2-A1)&" days, " & TEXT(MOD(A2-A1,1),"h"" hrs ""mm"" min ""ss"" sec""")


--ron
 
F

FrankM

This way did seem to work. Thank you for your reply, I'm gonna havta tuck
this one away in my memory. But I decided to use the INT calculation. There
seems to be more than one way to accomplish things.

Thank you!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top