# Thread: Time Calculation between two dates (2000 SR-1)

1. ## Time Calculation between two dates (2000 SR-1)

I need to calculate the time difference between a set date and time in cell A1 (19/04/04 09:00:00) to the current date and time

I have been using the formula =DATEDIF(A1,TODAY(),"d") which only gives me the number of days but now I need days,hours, minutes and seconds.

The result cell is custom number formatted as dd

Any help would be appreciated

Regards

2. ## Re: Time Calculation between two dates (2000 SR-1)

TODAY() just returns the current date, not the current time. If you need the date AND time, use NOW().

To get the difference between two date/times, just subtract them, and clear the formatting. The integer part is the number of days, the decimal part the hours etc. as a fraction of one day. Excel does not really provide one number format for accumulated time in hours and days. You could format the cell as [h]:mm:ss to see the difference in the form 37:12:58 (hours, minutes, seconds). Or split the result into two cells: =INT(NOW()-A1) for the number of days (clear the formatting) and =MOD(NOW()-A1,1) for the number of hours, minutes and seconds (apply a time format.)

3. ## Re: Time Calculation between two dates (2000 SR-1)

<pre>=now()-A1</pre>

will give the number of "decimal days" between now and the value in A1.

If the number of days is <31 you can use a custom format like:
d hh:mm:ss
to display the days, hrs, min secs in a form like:
24 10:06:32

or use a format like
d "days," h "hr," m"min, "s"sec"
24days, 10hr, 6min, 32sec

Unfortunately there is not a format to display the elapsed days, so if the days are >31 they will display incorrectly (after 31 you will start over at 1 and got to 29, then 1-31, 1-30, etc, they are the "days of the months"

You could use this formula (or a modification), though it will no longer be a number but will be a text value
<pre>=INT(NOW()-A1)&" days" & TEXT(MOD(NOW()-A1,1)," h""hr, "" m""min, ""ss"" sec""")</pre>

Steve

4. ## Re: Time Calculation between two dates (2000 SR-1)

Thank you Steve and Hans

Both recommendations work fine. I just have to decide now which method I am going to use.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•