Conditional formatting for date >= -30 days from todays date

Resolved · Low Priority · Version 2013

Thu 24 Oct 2013, 15:52 Delegate Siobhan said...

Siobhan has attended:
Excel Intermediate course
Excel Advanced course
Project Management - Framework & Processes course
PowerPoint Intermediate Advanced course

Conditional formatting for date >= -30 days from todays date

Hi There,

I am wanting to highlight cells that contain a date that is >=-30 days from todays date i.e. commence highlighting 30 days prior to the date reaching todays date.

I have tried entering a formula into the greater than conditional formatting but am not having success.

Any help appreciated.
I look forward to your response.

Thank you,
Siobhan

Thu 24 Oct 2013, 17:56 Edited on Thu 24 Oct 2013, 17:57 Trainer Gary said...

RE: Conditional formatting for date >= -30 days from todays date

Hi Siobhan

Thanks for getting in touch. This is a common tricky issue; you're not the first person to have come unstuck.

The formula you need is "less than =TODAY()-30". I've attached a workbook with an image to help explain this. The formatting is on cells C2:C11.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

GF Conditional Date Formatting.xlsx
GF-cond-form-date.png

Thu 31 Oct 2013: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips