Conditional Formatting Dates

[Tikey]

I am using Excel 2016 not 365.

I have a list of dates in the form 01/01/2018 which cover several years. I wish to be able to highlight the earliest ( and latest seperately ) date in the list based on the day and month only.

I'm struggling to do this, any help appreciated.

 

[Peter_SSs]

If this is not what you mean, can you explain which of my dates should be highlighted and why.

Excel Workbook
	A
2	08-January-2013
3	26-April-2015
4	22-February-2017
5	05-January-2019
6	06-July-2018
7	07-December-2016
8	28-November-2014
CF Dates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =MONTH(A2)*100+DAY(A2)=AGGREGATE(15,6,MONTH(A$2:A$8)*100+DAY(A$2:A$8),1)	Abc
A2	2. / Formula is =MONTH(A2)*100+DAY(A2)=AGGREGATE(14,6,MONTH(A$2:A$8)*100+DAY(A$2:A$8),1)	Abc

 

[Tikey]

Thanks for your reply.

The correct dates for what I am trying to achieve are highlighted in your column. I have tried to change your cell range ( A2:A8 ) to mine and replace A2 with the first cell ref in my range but it highlights all cells for me. In Conditional Formatting I am using "use a formula to determine which cells to format"

Can you provide any further guidence.

Thank you

 

[Peter_SSs]

Perhaps your dates are not real dates (numbers) but text
What happens if you use a blank cell with this formula pointed at one of your 'date' cells?
=ISTEXT(A3)


"use a formula to determine which cells to format" is correct.

 

[Tikey]

Using your formula changed to a cell (C3) in my list returns FALSE. All the cells are formatted as a date ie 01/01/2018.

 

[Peter_SSs]

Then if all your dates are real dates the CF should work. I suggest removing all CF from column C & then the steps would be:

Select from C2 (assuming first date is in C2) down to the end of the dates in column C (ensuring that C2 is the active cell after making the selection) & apply this CF, just changing each $8 to $x where x is the last row of your dates.

If still not working, what version of Excel & what operating system are you using?

Excel Workbook
	C
1
2	08-January-2013
3	26-April-2015
4	22-February-2017
5	05-January-2019
6	06-July-2018
7	07-December-2016
8	28-November-2014
9
CF Dates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =MONTH(C2)*100+DAY(C2)=AGGREGATE(15,6,MONTH(C$2:C$8)*100+DAY(C$2:C$8),1)	Abc
C2	2. / Formula is =MONTH(C2)*100+DAY(C2)=AGGREGATE(14,6,MONTH(C$2:C$8)*100+DAY(C$2:C$8),1)	Abc

 

[Tikey]

I've done as you suggested, which is what I had done previously with the same result, all cells are highlighted. I'm using W10, Excel 2016, not 365. In some other CF I have done I have had to put =C2= before the formula I am using, I have tried doing this but then no cells are highlighted.

 

[Peter_SSs]

Are you able to upload a copy of the workbook (with any sensitive data removed - really only need the dates in column C) to a file-share site (eg DropBox) and post a shared link here so I can take a look at what is going on?

 

[Tikey]

I've just used your formula on a new worksheet with a list of dates and it works fine. My list of dates are themselves derived from an Aggregate function, do you think that could be having an affect ?

 

[Peter_SSs]

My list of dates are themselves derived from an Aggregate function, do you think that could be having an affect ?

I wouldn't have thought so.