SOLVED
Home

excel date color

%3CLINGO-SUB%20id%3D%22lingo-sub-276199%22%20slang%3D%22en-US%22%3Eexcel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276199%22%20slang%3D%22en-US%22%3E%3CP%3EI%20change%20this%20calendar%20by%20changing%20B1.%3C%2FP%3E%3CP%3EResult%20is%20good%20expect%20color%20of%20highlighted%20dates.I%20want%20color%20of%20highlighted%20dates%20in%20column%20A%20and%20in%20other%20columns%20same%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281584%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281584%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much.%20Everything%20OK%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281519%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281519%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20George%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20easily%20customize%20each%20rule%20to%20show%20a%20specific%20number%20format.%3C%2FP%3E%3CP%3EIn%20the%20conditional%20formatting%20rule%2C%20you're%20not%20limited%20in%20the%20fill%20color%20only%2C%20you%20can%20change%20the%20font%20color%2C%20the%20number%20format%2C%20and%20more.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281495%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281495%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Sir%3C%2FP%3E%3CP%3EThere%20are%20three%20groups%3A-OFD%2C%20HLO%201%20and%20HLO%202%20in%20three%20colours.%3C%2FP%3E%3CP%3EOFD%20shift%20change%20in%20every%206th%20day%20starting%20Jan%203%202018%3C%2FP%3E%3CP%3EHLO%201%20in%20every%2011th%20day%20starting%20Jan%202%202018%3C%2FP%3E%3CP%3Eand%20HLO%202%20in%20every%2011th%20day%20starting%20Jan%207%202018%26nbsp%3B%20%26nbsp%3Bas%20in%20sheet%20%22Shift%20Dates%22.%3C%2FP%3E%3CP%3EPlease%20see%20the%20calendar%202018%26nbsp%3Bwhere%20I%20manually%20formatted%20each%20cell.%20Please%20help%20me%26nbsp%3B%20to%20auto%20format%20according%20to%20colour%20or%20date%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281230%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281230%22%20slang%3D%22en-US%22%3E%3CP%3EWell%2C%20highlight%20them%2C%20and%20press%20%3CSTRONG%3ECtrl%2B1%3C%2FSTRONG%3E%20to%20open%20the%20%3CSTRONG%3EFormat%20Cells%3C%2FSTRONG%3E%20dialog%20box.%3C%2FP%3E%3CP%3EThen%20select%20%3CSTRONG%3ECustom%3C%2FSTRONG%3E%2C%20and%20copy%20the%20format%20(ddd%20dd%20%22OFD%22)%20into%20the%20%3CSTRONG%3EType%3C%2FSTRONG%3E%20box%2C%20and%20then%20hit%20OK.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F58887i2CC715495EA75956%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Format%20Cells%20-%20Custom.png%22%20title%3D%22Format%20Cells%20-%20Custom.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281010%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281010%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Sir%3C%2FP%3E%3CP%3EI%20have%20updated%20my%20file%20.Please%20help%20me%20to%20format%20cells%20in%20page%20%22calendar%22%20so%20that%20it%20looks%20similar%20to%20cells%20in%20page%20%22Shift%20Dates%22%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280438%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280438%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Sir%3C%2FP%3E%3CP%3EWonderful%20.Working%20very%20good.Thank%20you%20very%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279796%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279796%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20really%20sorry%20about%20this%20buggy%20solution!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20realized%20that%20the%20solution%20is%20very%20difficult%20without%20a%20helper%20data%20source!%3C%2FP%3E%3CP%3ESo%20I%20suggest%20another%20solution%20by%20creating%20a%20list%20of%20the%20shift%20dates%20in%20a%20separate%20sheet%20and%20refer%20to%20this%20list%20in%20the%20conditional%20formatting%20rule%20of%20the%20calendar%20sheet%20by%20using%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DMATCH(C2%2C'Shift%20Dates'!%24A%242%3A%24A%242412%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20perfect%20and%20the%20most%20easier%20solution%20in%20this%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20it%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279528%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279528%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20sir%3C%2FP%3E%3CP%3Ejan%202%2C%202022%26nbsp%3B%20should%20be%20shift%20change%20but%20calendar%20shows%20jan%203%20%2C%202022.%20Is%20it%20Possible%20to%20copy%20color%20of%20dates%20from%20column%20B%20according%20to%20date%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279342%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279342%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20George%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20fixed%20this%20issue%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279001%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279001%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Sir%3C%2FP%3E%3CP%3EKindly%20check%20%3A-In%20the%20year%202021%20it%20reads%20Jan.3%20%2C2021%20as%20shift%20change%20date%20but%20it%20should%20be%26nbsp%3B%20jan%202%2C%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-278858%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-278858%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much.It%20works%20very%20good%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277774%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277774%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20George%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20done%2C%20but%20you%20need%20to%20a%20separate%20conditional%20formatting%20rule%20for%20each%20month%20and%20supported%20with%20a%20logic%20to%20deal%20with%20leap%20years.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20done%20for%20you!%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277711%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277711%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%3EDear%20Sir%3C%2FDIV%3E%3CDIV%3EThank%20you%20for%20your%20help.Highlighted%20cells%20in%20column%20B%20are%20dates%20of%20shift%20change%20(Every%206th%20day).First%20shift%20change%20was%20on%20Jan%203%2C%202018%20%2Cthen%20Jan%208%2C2018%2C%20and%20so%20on%20continuously.This%20is%20why%20I%20formatted%20column%20B%20.In%20Feb%202018%20dates%20are%20different%20and%20in%20march%20they%20are%20different%20and%20so%20on.It%20is%20different%20in%20the%20year%202020.%3C%2FDIV%3E%3CDIV%3EDates%20are%20different%20in%20month-wise%20and%20year-wise.%20When%20you%20select%20any%20year%20the%20days%20in%20that%20year%20should%20be%20highlighted%20same%20as%20in%20column%20B.%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276395%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20date%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276395%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20George%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EThe%20worksheet%20has%20complicated%20and%20unnecessary%20array%20formulas!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EBecause%20of%20this%2C%20it's%20too%20slow%20and%20takes%20a%20lot%20of%20time%20to%20calculate!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EYou%20can%20do%20it%20all%20in%20another%20way%20easier%2C%20faster%2C%20more%20efficiently%2C%20and%20more%20flexible%20so%20that%20you%20are%20not%20limited%20to%20the%20year%202020.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20updated%20the%20worksheet%20with%20a%20better%20approach%20and%20applied%20the%20conditional%20formatting%20rule%20to%20the%20other%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
george antony
Occasional Contributor

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

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

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

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

Thank you very much.It works very good

Dear Sir

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

Hi George,

 

I've fixed this issue in the attached file.

 

Regards

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

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

Dear Sir

Wonderful .Working very good.Thank you very much

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" 

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.

 

Format Cells - Custom.png

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

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.

Thank you very much. Everything OK

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies