Date Issue

[granianm]

Hi all, relatively new to excel and definitely to this forum so please be gentle!
I have a spreadsheet where I need to track when items need calibrating which is every 6 months. There is a column that records its last calibration date but what I would like is somehow for that column to be highlighted once they are due again, any ideas?

Cheers in advance!

 

[wideboydixon]

OK. Let's say that your dates are in column C. Highlight the column C and select Conditional Formatting>New Rule and then choose Use a formula to determine which cells to format. In the formula box, enter the following:

=AND($C1<>"",EDATE($C1,6)<=TODAY())

Click on Format... and choose a fill colour for the formatting.

Of course, you'll need to change column "C" to whichever column contains your dates.

WBD

 

[wideboydixon]

You should end up with something like this:

Book1
	C
1	Last Calibration Date
2	19/01/2017
3	19/02/2017
4	19/03/2017
5	19/04/2017
6	19/05/2017
7	19/06/2017
8	19/07/2017
9	19/08/2017
10	19/09/2017
11	19/10/2017
12	19/11/2017
13	19/12/2017
Sheet1

WBD

 

[granianm]

Perfection WBD, cheers!

 

[etaf]

i refreshed the page and saw no replies

then there was - so ignore

use conditional formatting to change the cell colour
when do you want that column to highlight - when the date is past ?
or a RAG - red , amber green
when its due in a week or 2 weeks

you would use a formula
+
=today() > date( year(Cell with last calibration date ), month(Cell with last calibration date ) + 6 ,day(Cell with last calibration date ))
see we are adding 6 to the months of the last date
and seeing if today() date is still in the future