Solved

date countdown in excel spreadsheet

Posted on 2010-09-18
11
1,105 Views
Last Modified: 2012-05-10
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
Comment
Question by:srodgers45
  • 6
  • 4
11 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33709697
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
 
LVL 81

Expert Comment

by:byundt
ID: 33709745
You could also use the DATEDIF function for the Conditional Formatting:
=DATEDIF(A1,TODAY(),"m")>=60
0
 

Author Comment

by:srodgers45
ID: 33711943
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
 
LVL 81

Expert Comment

by:byundt
ID: 33711956
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
 
LVL 81

Expert Comment

by:byundt
ID: 33711968
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:srodgers45
ID: 33712003
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
 
LVL 81

Expert Comment

by:byundt
ID: 33712140
=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
 

Author Comment

by:srodgers45
ID: 33712218
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
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 33712277
=IF(B3="","",B3-TODAY())
0
 

Author Closing Comment

by:srodgers45
ID: 33712374
Thanks for your patience and assistance with this, I know it was probably simple for you.
0
 
LVL 81

Expert Comment

by:byundt
ID: 33712419
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now