?
Solved

date countdown in excel spreadsheet

Posted on 2010-09-18
11
Medium Priority
?
1,281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 93

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Industry Leaders: 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!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 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