+ Reply to Thread
Results 1 to 15 of 15

Subtract dates d:hh:mm

  1. #1
    Registered User
    Join Date
    09-25-2008
    Location
    NJ
    Posts
    15

    Subtract dates d:hh:mm

    Seems that when I subtract a future date from NOW() and format the cell d:hh:mm that the result is 1 day off. So if I subtract NOW() (today is 10/24) from 10/25/08 it returns a 1 for the d when I would expect it to be zero.
    Last edited by Allardin; 10-24-2008 at 08:59 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    I get a zero -- if you format the cell with 25/10 in it as General what is the value you get ? or alternatively if you assume 25/10 is in B1 what does:

    =MOD(B1,1)

    return ?

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by Allardin View Post
    Seems that when I subtract a future date from NOW() and format the cell d:hh:mm that the result is 1 day off. So if I subtract NOW() (today is 10/24) from 10/25/08 it returns a 1 for the d when I would expect it to be zero.
    Using d:hh:mm format can be problematic.....

    d will never be greater than 31, for a start, so it's not a good format for calculating time periods 32 days or longer. Also the result can change if you are using 1904 date system, sounds like you are given the results you get.

    Check with

    Tools > Options > Calculation

    If you uncheck 1904 date system you should get zero as required but beware, any existing dates in your spreadsheet will change

    Note: The reason this happens is because the d format is not really designed for handling date differences, it's just giving the day of the month (hence why it doesn't go higher than 31) counting from 1900 or 1904 depending on the date system.

    But....."day 1" in 1904 date system starts a day (and 4 years) later....hence the discrepancy
    Last edited by daddylonglegs; 10-24-2008 at 07:45 AM.

  4. #4
    Registered User
    Join Date
    09-25-2008
    Location
    NJ
    Posts
    15
    What would be a way to subtract 2 dates and times and display the result as dd:hh:mm:ss
    Thanks

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    How about something like the below ?

    Where B1 = end date time, A1 start date time

    =INT(B1-A1)&":"&TEXT(MOD(B1-A1,1),"hh:mm:ss")

    (Untested...)

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    =B1-A1 works, just use a custom format of dd:hh:mm:ss

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    are you sure about that ?

    set date 1 to be say 1st April and date 2 to be 3rd May -- what answer do you get ?

  8. #8
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    I was assuming OP would take DLL's warnings about dd not supporting more than 31 days into account. If such is not the case, yours works better.

  9. #9
    Forum Contributor
    Join Date
    07-09-2008
    Location
    Dallas
    MS-Off Ver
    2007
    Posts
    121
    Beyond the answers, I'm curious; why would you expect the difference in days between 10/25 and 10/24 to be zero? There *is* a 1 day difference between today and tomorrow, yes?

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    You would expect it to be 0 if using say:

    (TODAY()+1)-NOW()

    ie the larger date is just a date value (no time), the smaller contains both date (integer) and time (decimal).

    NOW() (for me at 7:28PM UK) is 39745.83 (to 2 decimals) -- the .83 representing the time.
    Tomorrow (just date) is 39746

    39746-39745.83 < 1 thus no day has passed.

    Earlier post suggest may be a side effect of 1904 system (not tested myself but it does cause problems with most Date related functions) ... I thought possible that 2nd date set to have time value > time now()
    Last edited by DonkeyOte; 10-24-2008 at 03:01 PM.

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Nice to see you here Luke, a little slow at MrExcel?

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    Hi there... just broadening my horizons... are you too a member on MrE ?
    (I couldn't find a daddylonglegs... )

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    This is me

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    ah... good to know -- I'll take more care answering my date related posts from now on :-)

    have a good w/end...

  15. #15
    Registered User
    Join Date
    09-25-2008
    Location
    NJ
    Posts
    15
    thanks DonkeyOte that works
    Last edited by Allardin; 10-24-2008 at 08:49 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1