Solved

date countdown in excel spreadsheet

Posted on 2010-09-18
11
1,126 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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
 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

776 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