dates count till elapsed

[bladerunner926]

On a worksheet I have a date a person has passed a course say in cell a1
The course is valid for 5 years only before a person needs to requalify
in cell a2 I want to be able to calculate the time left between cell a1 and
5 years down the line and have it counting down
does anyone know of a formula etc how I can do this
Many Thanks

 

[T. Valko]

Try this:

A1 = some date

=EDATE(A1,60)-(A1+(TODAY()-A1))

Or, this version so it will flag an expired date:

=IF(EDATE(A1,60)-(A1+(TODAY()-A1))<0,"Expired",EDATE(A1,60)-(A1+(TODAY()-A1)))

Format the cell for either version as General of Number.

Note that EDATE requires the Analysis ToolPak add-in be installed for Excel
versions prior to Excel 2007.

 

[barry houdini]

Without Analysis ToolPak

=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))-TODAY()

format as general

 

[T. Valko]

The DATE function chokes on accountig for leap years depending on what you
think the correct result should be.

A1 = 2/29/2004

EDATE(A1,60) = 2/28/2009

DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) = 3/1/2009

I would think the correct result should be in the same month since the time
interval is "full years".