Highlight first occurrence of an entry every month

[bhenlee]

Hi experts,

I'm using this formula to highlight the first occurrence of each name in column A:

=IF(COUNTIF($A$4:$A$1000,A4)=1,0,COUNTIF($A$4:A4,A4)=1)

however, i need to modify this formula to highlight the first occurrence of each name each month of every year.

The dates are listed in column B.

Please help.

[ExcelFixer]

Its hard to picture what you are looking for, can you upload an example sheet?

[bhenlee]

the names are listed in column a and date in column B

so

John 08-13-2015 ---- highlight the name on this row
John 08-14-2015
John 08-15-2015
Anna 08-03-2015 ---- highlight the name on this row
Anna 08-04-2015
Anna 08-05-2015

hope this makes sense

[JohnTopley]

Are the dates always in sequence (ascending order)?

[ben_hensel]

Can you use column C as a helper with =DATE(YEAR(B1),MONTH(B1),1) etc in there? That will force the date to assess as the first of the month.

Then you could use COUNTIFS with the second criteria checking against the forced 1st as well.

[bhenlee]

yes they are in sequence but for each name the dates can start on any weekday of the month.

so:

John 08-01-2014 -- highlight name
John 08-02-2014
John 08-03-2014
John 08-03-2015 -- highlight name
John 08-04-2015
John 08-05-2015
Anna 08-19-2015 -- highlight name
Anna 08-20-2015
Anna 08-21-2015

[ExcelFixer]

Assuming you have names in column A and Dates in column the following conditional rule should work for you (they worked for me).

Please Login or Register  to view this content.

You might need a separate formula for the first row of data as that will not have a date to compare.

[bhenlee]

I had the conditional format already figured out and totally forgot this thread.

But i appreciate the time and effort buddy!!! thank you so much.

[Ichban]

I had the conditional format already figured out and totally forgot this thread.

But i appreciate the time and effort buddy!!! thank you so much.

Is it possible for you to share that formula? I am having similar problem.
I have personal expense tracker that contains date from 1/1/16-12/31/16
In Column A. All I am trying to do is conditionally format the entire
row for the first occurrence on a given month, e.g, if the first occurrence of expense takes place on 2/5/16 then I would like to highlight
the entire row for that date for the month of February.Similarly, if the first expense takes place in April 3 I want to get that row highlighted for April. I would if anyoneif anyone can help.

[Tony Valko]

The forum owner prefers that you start your own thread rather than asking your question in someone else's thread (even if it's related (yeah, I know, stupid rule)).