indicating due dates in excel file?

Question

I have a set date in the future on my speadsheet (example 11/02/2006), i want the cell to tell me 1 month before the due date. I just want a notification that i still have one month before the set due date. Thanks

Answer 1

Hi! This is the kind of question I love, so I hope this helps. There is an easy way to do this, (I tried a few more complex ways first before I smacked myself and started again! =) ) First we need to set up a cell that will hold a reference to the date 30 days in the future. Then we will use conditional formatting on the row that holds your due dates. Ready?

Go to a cell that will not be used in your spreadsheet (some column way off to the far right, but if possible stay up in the upper rows). I will use cell AA2 for this example.

Enter the following:
In cell AA1 - Warning Date
In cell AA2 - =TODAY()+30

Now AA2 should display a date 30 days from today.

Select the column where your due dates are.
Click the Format Menu
Choose Conditional Formatting

Using the dropdown arrows in each box make the top area of the Conditional Formatting dialog box read:

Cell Value Is Less Than Or Equal To $AA$2

(Make sure you include the dollar signs ($) in the cell reference)

Click the Format BUTTON in the Conditonal Formatting dialog box. Choose a format for the cells that are within a month of their due date. (Pattern = Yellow or whatever). Click OK in the Format Cells Dialog Box. Click OK in the Conditional Formatting Dialog Box.

That should do it!

Good Luck!

Jen

Answer 2

Excel actually processes dates as a number, counting "1" for each day since some 106 years in the past.
So, today's date would be 38987. But we usually format date cells so it shows a date we understand, and not that number.
But you can use this number to do straight-forward calculations, like the one you want.
Assume you have your target date (like your 11/02/2006) in cell A1, then you can put into cell A2 the following Excel formula:
=IF((A1-TODAY())<30, "Less than 1 month to go!","")
I found that Excel can sometimes be temperamental in handling dates as values. So, it is important that the cell with your target date (here A1) is first formatted as a Number, in which you enter the target date alphanumerically, like 2 Nov 06, hit Return: You see that 'magic number' I was talking about above. Then you enter the formula, and then you can change cell A1 to show a "proper" date by changing to date format.