highlight first day of the month on the column chart

Hi,

I have the column chart where on the horizontal axis I have dates.
Is it possible to highlight in any way (different colour, additional grid, whatever) the first day of each month or 7th day of the week (if I of course add that to my data)?

chart.PNG

Excellent!
Thanks a lot @KOKOSEK !

@rorya - could you please show me how to do it using formulas?

Hello again,

i am trying to implement this solution to my case and I found that did not tell that I have Pivot Chart instead of Chart.
In my case there is multicategory axis (date, weeknum) so I have difficulties with implementing above sulution to my case.

BTW. I realized that probably have never created Chart (not Pivot Chart) with multicategory level so needed to find some instructions on the web how to do it
Am I right that to do it, I need to have my data (date, weeknum, month, etc.) in adjacent columns couse only that way I am able to indicate data I want it to be my Category?
Is there possible then to achieve my goal on multicategory Chart built this way?

@KOKOSEK, what you have shown before is great but the main question from my site is there any different way to refer to category axis labels (multicategory) then merged string?

In attached sample I want to have on my chart the date as well as week number. Need to colour the first day of each month.
I referenced to category label (string) by a litte freaky formula. What if I'd like to show these data in a diffrent look (sheet sample2 - weeknumber above date)? Should I change formula in VBA?
Or is there any way to allow me reference to each category (date, weeknum) separatelly?

Excellent,
I wonder only why this feauture is not built in excel so the user could refer to the category name instead of part of the string created from all categories. If anybody change order of categories for better view, the macro should be adjusted

So....
Going further...
How would you manage with case when you need to highlight first day of the month but only from days that consists some data?
I prepared sample3. Would you do this similar?

The cons of that solution is:
- necessity to create additional columns in data table,
- neccesity to show "first day" on my chart below axis (am I able to hide it?),

What if I'd like to filter my data by additional fields? E.g 'colour' in sample4. My additional column consists additional criterias now.

What if my database contains data that can be filteres by colour, shape, size, material, etc. Should be always prepared tens of additional columns to specifi the first day of the month for each possible configuration?

Hi KOKOSEK,

Thanks for this.
This solution is very similar to mine, but is it better in any terms of? If not, I think I will stay with my one cause adding conditional formatting to my cells (hundreds of rows) may reduce performance of calculations.

Anyway...
This still is not the perfect sulution because I cannot see the first day of month when filter more than two colours

Nope,
I would like to highlight the first day of each month of those days that will be on the chart - depending on what filters I apply - e.g. two or three colors at the same time.