Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Subtracting 2 dates

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to subtract two dates and even when the two cells are blank, I get .01 in the third cell where the result should go.

    When I put in actual dates, the .01 gets added as an extra month.

    For example:
    Cell D22: 12/6/1994
    Cell D23: 8/9/2002
    Cell D24: 7.09

    Cell D24 is a custom formatted cell as yy.mm

    Any help on this is most appreciated.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-12 05:16, minnie wrote:
    I am trying to subtract two dates and even when the two cells are blank, I get .01 in the third cell where the result should go.

    When I put in actual dates, the .01 gets added as an extra month.

    For example:
    Cell D22: 12/6/1994
    Cell D23: 8/9/2002
    Cell D24: 7.09

    Cell D24 is a custom formatted cell as yy.mm

    Any help on this is most appreciated.
    In D24 enter:

    =IF(COUNT(D22:D23)=2,D23-D22,"")


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 05:16, minnie wrote:
    I am trying to subtract two dates and even when the two cells are blank, I get .01 in the third cell where the result should go.

    When I put in actual dates, the .01 gets added as an extra month.

    For example:
    Cell D22: 12/6/1994
    Cell D23: 8/9/2002
    Cell D24: 7.09

    Cell D24 is a custom formatted cell as yy.mm

    Any help on this is most appreciated.
    When you subtract date values you get a result in days -- 2,803 days for the two dates shown above. If you apply a date format to this result 2803 will be treated as the datevalue for 9/4/1911! This isn't a proper approach. Instead, use...

    =DATEDIF(D22,D23,"Y")+DATEDIF(D22,D23,"YM")/100

    ...to return 7.08.

    [ This Message was edited by: Mark W. on 2002-05-12 13:25 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    When you subtract date values you get a result in days -- 2,803 days for the two dates shown above. If you apply a date format to this result 2803 will be treated as the datevalue for 9/4/1911! This isn't a proper approach. Instead, use...

    =DATEDIF(D22,D23,"Y")+DATEDIF(D22,D23,"YM")/100

    ...to return 7.08.



    Good catch. But

    =IF(COUNT(D22:D23),YEARFRAC(D22,D23),"")

    will also do.

    Format the formula cell as General.

    PS. YEARFRAC is available only when the Analysis Toolpak add-in is activated thru Tools|Add-Ins.




    [ This Message was edited by: Aladin Akyurek on 2002-05-12 13:44 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 13:39, Aladin Akyurek wrote:
    When you subtract date values you get a result in days -- 2,803 days for the two dates shown above. If you apply a date format to this result 2803 will be treated as the datevalue for 9/4/1911! This isn't a proper approach. Instead, use...

    =DATEDIF(D22,D23,"Y")+DATEDIF(D22,D23,"YM")/100

    ...to return 7.08.



    Good catch. But

    =IF(COUNT(D22:D23),YEARFRAC(D22,D23),"")

    will also do.

    Format the formula cell as General.

    PS. YEARFRAC is available only when the Analysis Toolpak add-in is activated thru Tools|Add-Ins.


    [ This Message was edited by: Aladin Akyurek on 2002-05-12 13:44 ]
    =YEARFRAC(D22,D23) produces 7.675 rather than the desired 7.08.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,499
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default



    The YearFrac result looks correct

    consider
    =DATEDIF(C1,C2,"Y")+DATEDIF(C1,C2,"yd")/365
    = 7.674

    or =(F2-F1)/365.25 =7.674


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 14:13, Dave Patton wrote:


    The YearFrac result looks correct

    consider
    =DATEDIF(C1,C2,"Y")+DATEDIF(C1,C2,"yd")/365
    = 7.674

    or =(F2-F1)/365.25 =7.674

    7.675 isn't correct when the original posting requested results in the form... yy.mm

    Cell D24 is a custom formatted cell as yy.mm
    [ This Message was edited by: Mark W. on 2002-05-12 14:20 ]

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yearfrac would have to be modified by the following formula to present the result in fashion the original OP requested.

    =INT(D24)&"~"&TEXT(MOD(D24,1)*0.12,".00")
    this will result in 7~.08

    I am not a fan of mixing up things irrationally -- that's why I suggest including a separator character to alleviate confusion with true decimal notation.

    regards


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    I believe the OP was rather inquiring about the adverse effect of blanks (hence my attention on the control of blanks and inattention for the substraction itself in my original reply).

    Aladin

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmmm...

    =INT(YEARFRAC(D22,D23))&"~"&TEXT(MOD(YEARFRAC(D22,D23),1)*0.12,".00")

    versus...

    =DATEDIF(D22,D23,"Y")+DATEDIF(D22,D23,"YM")/100


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
  •