Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •