Conditional formatting using dates and using dates without years

My clients receive quarterly review over a period of 3-5 years. Each client has a different contract start date. I have 50+ clients to track and need, at a glance, to see when each client's next payment is due.

In my mind I have the idea of a sheet as follows:

Column A: Name of client
B: Contract start date
C: Date for Q1 review
D: Date for Q2 review
E: Date for Q3 review
F: Date for Q4 review

If the contract started on 01 October then their quarter dates would show as 01 Oct, 01 Jan, 01 Apr and 01 Jul.

I would like the cell with the next review date to automatically highlight (conditional formatting with a particular fill colour for example) and I would ideally like this to update itself every morning

I suspect that the main problem I have is that if I enter a date then it automatically adds a year to that date. What I actually need is for the cell to contain just the date and the month (as the quarterly reviews are obviously the same date and month each year).

Is there a way to get a cell to show only the date and month and is there a way, perhaps using the =TODAY() function to get it to highlight when one of the four quarter reviews is next?

Cheers.

hi,

Will you share sample excel file.

Rajan

Hi ,

for making a date use Date(Year, Month ,Day) formula..

If I get you wrong, then attach a sample file to help us help you better...


Don't forget to click *