Countdown formula

[Guest]

I need to track the expiration of a specific period of time -- 6 months --
via a countdown cell.

Basically, I have a column (i.e., A1-A150) where I will be entering a date.
I will need to formulate another column whose cells (i.e., C1-C150) countdown
six months from the date entered in the corresponding row (i.e., C1 counts
from the date in A1; C35 counts from the date in C35).

Any advice on this would be greatly appreciated!

Some other questions:
1) Is there a specific format I should enter the dates in to ensure
countdown accuracy?
2) Is there a way, once the countdown period expires, to conditionally
format the cells so that they turn red, or even email me an alert? (The
former seems possible, not sure about the latter, though)
3) Is there a way to coundown this period so that it alerts me, say, ten
days before the six month period elapses?

Thanks so much in advance!

 

[Bob Phillips]

C1: =IF(A1<TODAY()+60,A1-TODAY() &" days to go","")

Any date format should be okay, you only want the number of days I presume?

Just setup CF with a formula of =A1+60<TODAY().

Again use CF with a formula of =A1+50<TODAY(), but make sure that the other
one goes first in the order.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[SteveG]

This works if your 6 month date is considered to be the same day as your
start day. i.e. 10/21/2005 + 6 months = 4/21/2006.

=EDATE(A1,6)-TODAY()

This sees 182 days between 10/21/2005 and 4/21/2006 and subtracts the
days left from the end date resulting in 93 days left. 89 have already
gone by.


HTH

Steve

 

[Guest]

Thanks so much for the response, Bill -- very much appreciated!

I'm just having trouble understanding exactly how to set up this formula
from your explanation. If A1 is 1/1/2006, I need the cell to reflect 6 months
from Jan 1-June 1, or 182 days (or alternately 6 months minus 10 days, or 172
days, if I want to be alerted 10 days in advance of the 6 month expiration).

Sorry to be so obtuse, but how exactly should the formula be entered? I
don't understand how to set it up with the base formula:

C1: =IF(A1<TODAY()+60,A1-TODAY() &" days to go","")

and the two alaternate edits:

=A1+60<TODAY()
=A1+50<TODAY()

Thanks again,

Dan

 

[Guest]

Thanks so much, Steve, for your help as well.

Indeed, if I enter 10/21/2005, I want a countdown until the date 4/21/2006.

Entering the formula as you wrote it, =EDATE(A1,6)-TODAY() gives me a #NAME?
error. Any ideas to correct? Does the entered date in A1 have to be in a
specific format?

 

[SteveG]

Dan,

The NAME error means that excel is not recognizing the EDATE formula.
You need to add-in the Analysis ToolPak from the Add-Ins menu. Go to
Tools>Add-Ins, when the menu pops up, select the checkbox next to
Analysis ToolPak and click on OK. It will then ask if you want to
install the add-in. Click yes or ok. It will install for you. Once
it is done, try the formula again and it should work without a
problem.

HTH
Steve

 

[Guest]

Fantastic, Steve - that worked. I had to reformat the EDATE formula cell to a
number, and it seems to work great.

I assume that in order to give me a date that is 10 days before the 6-month
expiration, I would need the formula:

=EDATE(A1,6)-TODAY()-10

Thanks again,

Dan