dates count till elapsed

B

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

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.
 
B

barry houdini

Without Analysis ToolPak

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

format as general
 
T

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".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top