Forum Discussion

Copper Contributor

Oct 22, 2018

Solved

excel date color

I change this calendar by changing B1. Result is good expect color of highlighted dates.I want color of highlighted dates in column A and in other columns same .

George1.xlsm9.3 MB

george antony
Oct 28, 2018
Thank you very much.It works very good

Haytham Amairah

Silver Contributor

Oct 22, 2018

Hi George,

The worksheet has complicated and unnecessary array formulas!

Because of this, it's too slow and takes a lot of time to calculate!

You can do it all in another way easier, faster, more efficiently, and more flexible so that you are not limited to the year 2020.

I've updated the worksheet with a better approach and applied the conditional formatting rule to the other columns.

Please find the attached file.

Hope that helps

Year Calendar.xlsx14 KB

george antony

Copper Contributor

Oct 25, 2018

Dear Sir

Thank you for your help.Highlighted cells in column B are dates of shift change (Every 6th day).First shift change was on Jan 3, 2018 ,then Jan 8,2018, and so on continuously.This is why I formatted column B .In Feb 2018 dates are different and in march they are different and so on.It is different in the year 2020.

Dates are different in month-wise and year-wise. When you select any year the days in that year should be highlighted same as in column B.

Year Calendar online.xlsx34 KB

Haytham Amairah
Silver Contributor
Oct 25, 2018
Hi George,

This can be done, but you need to a separate conditional formatting rule for each month and supported with a logic to deal with leap years.

I've done for you!
Please find the attached file.

Regards,
Haytham
Year Calendar V2.xlsx35 KB
- george antony
  Copper Contributor
  Oct 28, 2018
  Thank you very much.It works very good
  - george antony
    Copper Contributor
    Oct 29, 2018
    Dear Sir
    Kindly check :-In the year 2021 it reads Jan.3 ,2021 as shift change date but it should be jan 2, 2021.