Drop down list with Countdown formula?

[inteliss]

Hi all,

I'm looking to create a drop down list (or another list you is more suitable) which lets me select time periods - specifically 2 weeks, 2 months and 6 months.
(currently we only need these three options).

Then I was hoping there's a formula which helps me countdown the time-frame (chosen from the drop down list) from the date that will be entered into another cell.

For example,
In cell E4 i will enter in a date e.g. 05/02/2013, then in F4 there will be a dropdown list - I choose 2 months. It should start to countdown on cell G4 and hopefully G4 can be highlighted to red/ or display 'ACT NOW' when the 2 months is up.


Please let me know if I haven't made this clear.
Thank you in advance!

Looking forward to any hints/helps.

Inteliss.

[dredwolf]

Without a workbook to see the data, I would look into the TODAY() or NOW() functions, use either of them for your counter ( in a referenced cell, preferably; But can be used within the formula as well), JUST BE AWARE, they are functions that recalculate every time the sheet/workbook recalculates, so they can be time hogs )

Hope this helps

[inteliss]

Hi and thanks for the reply but I'm a total newbie when it comes to Excel, could you be more specific?

Actually I haven't got any data as of yet.
Here's how it should look like.

Column A (date) Column B (dropdown list) Column C (days left)
01/02/2013 14 days 9 days <-- this cell should be highlighted when the time is up e.g. 9 days later

[dredwolf]

Sorry about delay getting back to you, had not gotten the email saying you responded

To get the value for C (assuming actual data starts in row 2) :
something like this in C2:
=IF(B2="","",B2-(TODAY()-A2))
drag down

to get it the cell to highlight:
select the cell(s) to apply this to (say c2:c20 for example)
click Conditional Formatting, select new rule,select use formula..
in the formula input bar enter this
=AND(ISNUMBER(C2),C2<=0)
hit format, select fill, choose a color, ok,ok

Hope this helps