Dates subtracted incorrectly

[Guest]

When subtracting dates as is 1 June 2005 - 3 May 1981 in date format within Excel I end up with an extra month. It should come out around 24 year and 29 day but instead I get 24 yrs, 1 mnth, 29 days. Any ideas?

 

[Jan Karel Pieterse]

Hi Toddrd,

When subtracting dates as is 1 June 2005 - 3 May 1981 in date format within Excel I end up with an extra month. It should come out around 24 year and 29 day but instead I get 24 yrs, 1 mnth, 29 days. Any ideas?

Use this formula:

=DATEDIF(A4,A5,"md") & "-" & DATEDIF(A4,A5,"ym") & "-" & DATEDIF(A4,A5,"y")

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Jan Karel Pieterse]

Hi Toddrd,

<<First, thank you for the response to my question. I may have my data in a format that doesn't work with your formula.
The attached shows the error that I mentioned highlighted in red. My date format is custom YY-MM-DD throughout to
show amounts of time when adding or subtracting. I tried to apply your formula and it didn't work. I have an example
below. My simple formula is the third line and yours (adapted) is the next line. Any thoughts? Thanks in advance. I
apologize for the question if it is too basic.>>

No question is too basic.

One of both dates is considered to be text or the order is the wrong way around.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com