Oct 21 2018 10:55 PM
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 .
Oct 22 2018 09:02 AM
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
Oct 24 2018 10:02 PM
Oct 25 2018 01:51 AM
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
Oct 28 2018 10:44 PM
Dear Sir
Kindly check :-In the year 2021 it reads Jan.3 ,2021 as shift change date but it should be jan 2, 2021.
Oct 29 2018 12:27 PM
Hi George,
I've fixed this issue in the attached file.
Regards
Oct 29 2018 10:15 PM
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
Oct 30 2018 08:29 AM
I'm really sorry about this buggy solution!
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
Nov 01 2018 01:42 AM
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"
Nov 01 2018 09:02 AM
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.
Nov 01 2018 11:49 PM
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
Nov 02 2018 01:20 AM
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.
Oct 27 2018 10:27 PM
SolutionThank you very much.It works very good