• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1438
  • Last Modified:

date countdown in excel spreadsheet

I want to take a date and create a countdown for expiration after so many months have passed. for example: Inservice 09/01/2010, expires 60 months from now, with end date that will change font to red on that date.
0
srodgers45
Asked:
srodgers45
  • 6
  • 4
1 Solution
 
Patrick MatthewsCommented:
If you simply want the font to be red once that due date has been achieved, use Conditional Formatting, with a formula based rule such as (assuming the data in question is in cell A2):=TODAY()>=DATE(YEAR($A$2)+5,MONTH($A$2),DAY($A$2))
0
 
byundtCommented:
You could also use the DATEDIF function for the Conditional Formatting:
=DATEDIF(A1,TODAY(),"m")>=60
0
 
srodgers45Author Commented:
how do i insert the formula in to mutiple cells without changing each one? i have to input mutiple items

Inservice Date:                  expires:              Days until Expiration

09/01/2010                        09/01/2010           1836 (this cell would have formula and would turn red
09/15/2010                        09/15/2010                      30 days prior to expiration date)
etc.....

Thanks for the help so far, i do not use excel that often.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
byundtCommented:
1) Select the cell with conditional formatting set up correctly
2) Copy it
3) Select the other cells that need the formatting
4) Edit...Paste Special...Formats

You could also use the Paintbrush icon to copy the formatting:
1) Select the cell with conditional formatting set up correctly
2) Click the Paintbrush icon
3) Select the other cells that need the formatting
0
 
byundtCommented:
If you are copying the conditional formatting, you need to make sure that the formula uses relative addressing (no dollar signs). Excel will update the cell reference correctly for you.
=TODAY()>=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))          Patrick's suggestion for cell A2
=DATEDIF(A2,TODAY(),"m")>=60                                     Brad's suggestion for cell A2


Brad
0
 
srodgers45Author Commented:
inservice      replace      days until expiration
            
9/1/2005      9/1/2009      1461
9/1/2008      10/1/2010      760

This is what the current setup looks like, i tried to use the formulas above for the column title of "Days until Expiration" it came up with errors?  the current formula is just = b3-a3 which gives a static number, i am looking to base the countdown it on current date. The top one should be a negative number. If that makes sense? I am sure it's simple, just not familiar with it................ Thanks
0
 
byundtCommented:
=B3-TODAY()                  returns the days until expiration, with a negative number when past expiration
=MAX(0,B3-TODAY())        returns the days until expiration, or 0 if past expiration
0
 
srodgers45Author Commented:
OK that works, i new it was something simple, one last question. How do i insert the formula to the whole column without showing the numbers until the dates are entered? (the -40,440 populates all the cells the formula was pasted to?)

In Service      Replace      Days to Expiration
            
9/1/04      8/1/10      -49
9/1/08      9/9/09      -375
10/1/10      10/1/15      1,838
8/31/08      10/31/08      -688
7/4/04      10/1/10      12
            -40,440
            -40,440
            -40,440
            -40,440
0
 
byundtCommented:
=IF(B3="","",B3-TODAY())
0
 
srodgers45Author Commented:
Thanks for your patience and assistance with this, I know it was probably simple for you.
0
 
byundtCommented:
srodgers45,
The question wasn't difficult to answer, but it took a while before it was asked. That's a common occurrence here on Experts Exchange, however.

Thanks for the kind words and grade!

Brad
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now