Time Calculation between two dates (2000 SR-1)

Topic

New Reply

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

Reply | Quote

Replies

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.)

Reply | Quote

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.)

Reply | Quote

=now()-A1

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

If the number of days is 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

=INT(NOW()-A1)&" days" & TEXT(MOD(NOW()-A1,1)," h""hr, "" m""min, ""ss"" sec""")

Steve

Reply | Quote

Thank you Steve and Hans

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

Reply | Quote

Thank you Steve and Hans

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

Reply | Quote

=now()-A1

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

If the number of days is 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

=INT(NOW()-A1)&" days" & TEXT(MOD(NOW()-A1,1)," h""hr, "" m""min, ""ss"" sec""")

Steve

Reply | Quote