Subtracting 2 dates

minnie

New Member
Joined
Apr 25, 2002
Messages
28
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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,"")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
The YearFrac result looks correct

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

or =(F2-F1)/365.25 =7.674
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hmmm...

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

versus...

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

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top