Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excel 2013: Subtracting dates

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel 2013: Subtracting dates

    I am subtracting dates in Excel 2013. This works fine, except where the result should be 00:00 (hours:minutes), which appears as -00:00. How can I make this appear just as 00:00?

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    Can you post two sample dates that are creating this error?

  3. #3
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    Does it work if you round to the nearest minute, i.e. with this formula

    =MROUND(A2-B2,"0:01")

  4. #4
    New Member
    Join Date
    Jan 2010
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    Thanks for the very quick replies. I've discovered that it's not subtracting the dates that's the issue, but the fact that I'm embedding this inside another formula. The complete formula I'm trying to use is:

    =IF(A9<=TODAY(),B9-C9,"")

    Where A9 is a date, and B9 and C9 are two times. I only want the difference between the two times to display if the the date in A9 is today or a date in the past. This works, except if the difference between the two times is 0 minutes, the formula returns -00:00. If I just do B9-C9, Excel correctly displays 00:00.

  5. #5
    New Member
    Join Date
    Jan 2010
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    Quote Originally Posted by barry houdini View Post
    Does it work if you round to the nearest minute, i.e. with this formula

    =MROUND(A2-B2,"0:01")
    I can't get this to work at all...

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    Maybe this?

    =IF(A9<=TODAY(),ABS(B9-C9),"")

    Note: This formula makes all date differences positive.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    My suggestion within your formula will look like this

    =IF(A9<=TODAY(),MROUND(B9-C9,"0:01"),"")

  8. #8
    New Member
    Join Date
    Jan 2010
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    Quote Originally Posted by Rick Rothstein View Post
    Maybe this?

    =IF(A9<=TODAY(),ABS(B9-C9),"")

    Note: This formula makes all date differences positive.
    Thanks, Rick. I'm afraid this won't work, though, because sometimes the result should be negative (it's just that 0:00 should (can!) never be negative!

  9. #9
    New Member
    Join Date
    Jan 2010
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    Quote Originally Posted by barry houdini View Post
    My suggestion within your formula will look like this

    =IF(A9<=TODAY(),MROUND(B9-C9,"0:01"),"")
    Thanks, Barry. I'd tried this, but have tried it again. Excel just returns "0:00" regardless of what the actual difference between B9 and C9 is...

  10. #10
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013: Subtracting dates

    OK, it seems to work for me, how is the result cell formatted?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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