Conditional Formatting - Dates before 365 days EXCEPT last line

I have conditionally formatted a worksheet with =$C6<=TODAY()-365, meaning that any lines with a date in Column C older than 1 year ago are filled with colour.

Is there any way of allowing me to hide all of the coloured rows EXCEPT the last one, so I can identify that there is archived data hidden (I know the row count will be 1,2,3,4,5,26,27,28,29).

So, I was thinking either:
Conditionally format but make the final line a different colour some way so that I could use Filter/Filter by Colour and hide Fill Colour 1, Do not hide Fill Colour 2 or No Fill
or
If there is any way of using any of the alternative filter options to not hide everything, just all of the lines except the final coloured one

There is no consistency between dates on differing tabs - it is for tracking absences from work - so would need to be a "one size fits all"

So, if your rows are 1,2,3,4,5,26,27,28, and 29, do you only want row 29 to be visible, or row 5 also - this is not the last one, but is the last of a contiguous group.

Off the top of my head I was thinking that you could put a formula in a column somewhere, e.g. column H, which is based around that CF formula (which will return TRUE or FALSE) but also includes another term to detect if it is the last one,although that might be difficult if you want both row 5 and row 29 to be visible. Anyway, continuing along those lines you could then filter based on the cells in row H.

It would help if you attached a sample Excel workbook, as all we know from your description is that column C contains a date. The yellow banner at the top of the screen has details of how to do this.

Hope this helps.

Pete

I can't see an attachment.

Pete

there you go

You can use this formula in J6:

=IF(AND($C6<=TODAY()-365,COUNTIF($C$6:$C6,"<"&TODAY()-365)<COUNTIFS($C$6:$C$34,"<>",$C$6:$C$34,"<="&TODAY()-365)),"Hide","")

and copy down to J34. Put a suitable heading in J5 (e.g. Display) and select the range J5:J34 then click on Data | Autofilter. Then in the filter drop-down in J5 you can unselect Hide, so that only blanks in that column are shown, which will include the final row which is older than 1 year. It is not a good idea to have a table next to the main table for Other Leave, as the hidden rows will also hide the rows of that table, so maybe you should move this to below the main table.

Hope this helps.

Pete

That is perfect. I am not going to ad d a header, and have "hide" in white text so that "hide" is hidden.

I know what you mean about Other Leave - it was a workbook I inherited, so wanting to get other things polished off before i start to worry about that.

Cheers for the help.

Darryl

You're welcome, and thanks for the rep.

Pete