Solved

date countdown in excel spreadsheet

Posted on 2010-09-18
11
1,060 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 80

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 80

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 80

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 80

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 80

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 80

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 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

10 Experts available now in Live!

Get 1:1 Help Now