Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.
Date formulas
Name: rickang Date: February 24, 2009 at 02:22:29 Pacific OS: Windows XP Subcategory: General
Comment:
Hi,
i wish to place a date into a cell, however i want it to change colour or shade red when it expires over a certain date. eg 29/02/09, shades red when it passes 2 years from that date.
Name: Mike (by mmcconaghy) Date: February 24, 2009 at 10:55:18 Pacific
Reply:
The simplest way would be something like this, assuming your DATE is in cell A1:
1st - Highlight Data Cell I.E. Cell A1
On the Menu Bar:
2nd - Format 3rd - Conditional Formatting 4th – Change “Cell Value is” to “Formula Is” 5th – Enter the formula:
=AND((A1+731)=TODAY())
Sub Menu Format : 6th - Patterns 7th - Choose a pretty color 8th - Press OK 9th - Press OK
You are simply adding 731 days to the date in cell A1 and when they match your cell changes color.
There are probably other ways of doing it, but this works.
MIKE
0
Response Number 2
Name: rickang Date: February 24, 2009 at 13:24:10 Pacific
Reply:
i cannot seem to get it to work.
Thanks anyway
0
Response Number 3
Name: Mike (by mmcconaghy) Date: February 24, 2009 at 13:58:13 Pacific
Reply:
What happens? or doesn't happen? Is the target cell formated at a date?
MIKE
0
Response Number 4
Name: rickang Date: February 24, 2009 at 14:16:59 Pacific
Reply:
nothing, what ever date i input it remains the same. Ive inputted a date over 2 days ago and entered the formula on the conditional format. In theory then this should shade out straight away on pressing ok. It must be something that im doing wrong!
0
Response Number 5
Name: rickang Date: February 24, 2009 at 14:18:53 Pacific
Reply:
sorry i inputted a date over 2 years ago not 2 days ago.
0
Response Number 6
Name: Mike (by mmcconaghy) Date: February 24, 2009 at 14:36:23 Pacific
Reply:
The original formula only works on the Exact date two years in the future.
For greater than or equal to, two years try this:
=AND((A1+732)>TODAY())
Notice the change from Equals to Greater Than and 731 days to 732 days
Summary: for excel 2003...... i have a large range of cells that have mostly different dates, some are similiar. What i need is a formula to count th dates that are within the last 30 days from what would be t...
Summary: I'm currently working on a Access project, and I am in need of a certain validation rule. I am trying to find a rule which will only allow dates to be chosen from "todays date" up until 2 months in fr...
Summary: So the queries are adding the numbers found in the 2 fields as long as the record contains a specified date? What fields do you want to contain a zero? What formula are you using, and in what field/q...
0 
