• 514K Members
• 3,359 Online
• 611K Conversations
SOLVED

## excel date color

Occasional Contributor

# 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 .

14 Replies

# Re: excel date color

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

# Re: excel date color

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.

# Re: excel date color

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

Solution

# Re: excel date color

Thank you very much.It works very good

# Re: excel date color

Dear Sir

Kindly check :-In the year 2021 it reads Jan.3 ,2021 as shift change date but it should be  jan 2, 2021.

# Re: excel date color

Hi George,

I've fixed this issue in the attached file.

Regards

# Re: excel date color

Hello sir

jan 2, 2022  should be shift change but calendar shows jan 3 , 2022. Is it Possible to copy color of dates from column B according to date

Highlighted

# Re: excel date color

Now I realized that the solution is very difficult without a helper data source!

So I suggest another solution by creating a list of the shift dates in a separate sheet and refer to this list in the conditional formatting rule of the calendar sheet by using this formula:

`=MATCH(C2,'Shift Dates'!\$A\$2:\$A\$2412,0)`

This is the perfect and the most easier solution in this case.

Please find it in the attached file.

Hope that helps

# Re: excel date color

Dear Sir

Wonderful .Working very good.Thank you very much

# Re: excel date color

Dear Sir

I have updated my file .Please help me to format cells in page "calendar" so that it looks similar to cells in page "Shift Dates"

# Re: excel date color

Well, highlight them, and press Ctrl+1 to open the Format Cells dialog box.

Then select Custom, and copy the format (ddd dd "OFD") into the Type box, and then hit OK.

# Re: excel date color

Hello Sir

There are three groups:-OFD, HLO 1 and HLO 2 in three colours.

OFD shift change in every 6th day starting Jan 3 2018

HLO 1 in every 11th day starting Jan 2 2018

and HLO 2 in every 11th day starting Jan 7 2018   as in sheet "Shift Dates".

Please see the calendar 2018 where I manually formatted each cell. Please help me  to auto format according to colour or date

# Re: excel date color

Hi George,

You can easily customize each rule to show a specific number format.

In the conditional formatting rule, you're not limited in the fill color only, you can change the font color, the number format, and more.

Please find the attached file.

# Re: excel date color

Thank you very much. Everything OK

Related Conversations
Automatic coloring sunburst chart
stefan645 in Excel on
2 Replies
Cell color
DaneeTewell in Excel on
0 Replies
Date Formatting
Mayada Basha in Excel on
1 Replies
Copy/paste no longer working in Excel
Jon Firooz in Excel on
76 Replies