Datedif but for date in future and to return formatted time value

[jakey123]

Hi,
I need to calculate the difference in Years, Months and Days between:

Date 1 = TODAY()
Date 2 = 4 years after a date in cell A1, which will always be earlier than today's date

(A bit of backround - I have certain risk management procedures that have a lifespan of 4 years. I want to calculate the time between now and 4 years after the date the procedure was completed, essentially to see how long before they have to be redone).

So far I have:

=DATEDIF(A1+4,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"

But that returns #NUM!.

Removing the +4 obviously just calculated the difference between the date in A1 and today, but I need the date in A1 PLUS 4 years and today.

I have also tried:

=(DATE(YEAR(A1)+4,MONTH(A1),DAY(A1))-TODAY())/365.25

which works in theory, however:
a) no consideration for leap years
b) does not return nY, nM, nD - only the decimal.

However I would be happy to use this method if I could convert it to Years Months Days.

Any help would be very much appreciated. Thanks.

[NBVC]

Maybe you want?

=DATEDIF(DATE(YEAR(A1)+4,MONTH(A1),DAY(A1)),TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"

or

=DATEDIF(A1,TODAY(),"y")-4&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d" might work too.

[jakey123]

Your 2nd solution works up to a point (thanks very much).

If the date in A1 is today's date, then the cell correctly returns 4 years. Similarly, if the date is exactly 4 years ago, then it returns 0y0m0d, again, correctly.

However, there is something very strange - entering 5 / 8 / 08, ie a year ago today, and it returns 3 years which is correct. However, enter 6 / 8 / 08, and it returns 4y 11m 30d.

So any date between exactly a year minus a day ago and today is 1 year too much. How can it be edited to solve this?

(I would also be extremely grateful if you could briefly tell me why your 2nd solution works!) Thanks again.

[NBVC]

Try:

=DATEDIF(A1,TODAY(),"y")-IF(TODAY()-A1<365,3,4)&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"

[jakey123]

Hi - i still can't get it to work 100% correctly (I did try a couple of IF commands first time round):

If A1 = 06/08/2008 (ie exactly a year ago), then it correctly returns 3 years.

But if A1 is 1 day later, ie 07/08/2008, then instead of 3 years and 1 day it returns 3 years 11m and 30d - basically the reverse.

Furthermore, setting A1 as today's date 06/08/2009 - it now returns 3 years instead of 4!

It's just this final year between 2008-2009 it seems to get confused about.

Any tips? thanks very much again.

EDIT:

I have also tried:

=((A1+1461)-TODAY())/365.25

(where 1461 = 365.25 x 4).

However I get a decimal result, so if I convert that into nY nM nD then I wouldn't need datedif, except that leap years would not be included in the calculation (whereas they would, if I am correct, in the DATEDIF function). thanks.

[NBVC]

What would you expect then of August 5, 2008 entry?

[jakey123]

5th Aug 08 I would expect about 2y 11m and about 30 days, as that is the difference between todays date (6/8/09) and 4 years after the 5th aug 08.

At present, the formula returns 3y 0m 1d.

Changing the 5th to the 7th (two days later), and it returns 3y 11m 30d. So two days and it changes the result by over 11 months! very strange.

I am also trying:

=YEAR(TODAY())-YEAR(A1+1461)-IF(OR(MONTH(TODAY())<MONTH(A1+1461),AND(MONTH(TODAY())=MONTH(A1+1461),
DAY(A2)<DAY(A1+1461))),1,0)&" y "&MONTH(TODAY())-MONTH(A1+1461)+IF(AND(MONTH(TODAY())
<=MONTH(A1+1461),DAY(TODAY())<DAY(A1+1461)),11,IF(AND(MONTH(TODAY())<MONTH(A1+1461),DAY(TODAY())
>=DAY(A1+1461)),12,IF(AND(MONTH(TODAY())>MONTH(A1+1461),DAY(TODAY())<DAY(A1+1461)),-1)))&"m "&TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY())-IF(DAY(TODAY())<DAY(A1+1461),1,0),DAY(A1+1461))&" d"

But that is also giving me all sorts of strange results.

I didn't know calculating the difference between a date in the past + 4years and today's date could be so difficult! Cheers for all your assistance though.

[daddylonglegs]

Try like this

=DATEDIF(TODAY(),A1+1461,"y")&"y "&DATEDIF(TODAY(),A1+1461,"ym")&"m "&DATEDIF(TODAY(),A1+1461,"md")&"d"

although if you have any dates in A1 more than 4 years old you'll get an error.....

[jakey123]

That works fine but does that take into account leap years?

Also, if A1 is more than 4 years away (I know at the moment it throws up an error), is it possible to have it return a "negative" date difference, i.e. days overdue? (It can come up in red or something).??

Many thanks.

[daddylonglegs]

Every 4 year period contains exactly 1 leap day, hence 1461 days...until you get to the year 2100 which is the next year divisible by 4 that isn't a leap year. If you really want to cater for dates that far in advance then you can replace A1+1461 with either DATE(YEAR(A1)+4,MONTH(A1),DAY(A1)) or, using Analysis ToolPak addin functions, EDATE(A1,48).

To cope with dates more than 4 years old try changing the formula to the following:

=DATEDIF(MIN(TODAY(),A1+1461),MAX(TODAY(),A1+1461),"y")&"y "&DATEDIF(MIN(TODAY(),A1+1461),MAX(TODAY(),A1+1461),"ym")&"m "&DATEDIF(MIN(TODAY(),A1+1461),MAX(TODAY(),A1+1461),"md")&"d"

This always gives a positive result so to distinguish the negative results you can use conditional formatting to turn those red, i.e. select the cell with the formula and use "formula is" option with this formula in conditional formatting

=A1+1461<TODAY()

and apply red font

[jakey123]

It works beautfiully - thank you very much (still trying to figure out exactly how it works but oh well!).

However, I "lied" when I said the date was A1 (was for simplicity more than anything else). The date is actually in a separate sheet, and conditional formatting as I'm sure you know doesn't work across sheets.

So, short of copying the entire set of dates for each client into the current worksheet and referencing the date in the conditional formatting box to that, is there any way to "reverse engineer" the nY nM nD to reach the date that was "A1", for use in the cond.format. formula field., to allow the red to appear if a positive number?

I tried but can't get round the fact that your formula returns a text result (I know there is a way to get excel to analyse that but not too sure how). If you think it would just be easier to copy the data into the current worksheet and hide the cells, please do so and that will be fine! Thanks again.

[NBVC]

However, I "lied" when I said the date was A1 (was for simplicity more than anything else). The date is actually in a separate sheet, and conditional formatting as I'm sure you know doesn't work across sheets.

You can work around this...

You can indirectly reference the cell in the "other" sheet, e.g. =Indirect(Sheet1!A1)

or you can name that cell, something like Result

and reference that in your Conditional Formatting formula...