Subtracting 2 dates

[minnie]

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.

[Aladin Akyurek]

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,"")

[Mark W.]

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 ]

[Aladin Akyurek]

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 ]

[Mark W.]

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.

[Dave Patton]

The YearFrac result looks correct

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

or =(F2-F1)/365.25 =7.674

[Mark W.]

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 ]

[Yogi Anand]

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

[Aladin Akyurek]

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

[Mark W.]

Hmmm...

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

versus...

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