Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countdown formula
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countdown formula
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) "Dan" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countdown formula
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=502507 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countdown formula
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 "Bob Phillips" wrote: 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) "Dan" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countdown formula
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" wrote: 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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=502507 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countdown formula
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 ToolsAdd-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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=502507 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countdown formula
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 "SteveG" wrote: 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 ToolsAdd-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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=502507 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |