Home

Conditional Formatting dates

%3CLINGO-SUB%20id%3D%22lingo-sub-822364%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822364%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20to%20columns%20of%20dates%20A%20%26amp%3B%20B.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20color%20the%20cell%20in%20column%20A%20red%20if%20the%20date%20is%20later%20then%20the%20date%20in%20the%20cell%20in%20column%20B%20in%20the%20same%20row.%3C%2FP%3E%3CP%3Eneed%20to%20do%20this%20to%20all%20rows...%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ERoi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-822364%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822374%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398327%22%20target%3D%22_blank%22%3E%40ROI_NOIF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Roi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20your%20data%20starts%20from%20A1%2C%20select%20column%20A%20and%20apply%20conditional%20formatting%20rule%20with%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%24A1%26gt%3B%24B1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822378%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822378%22%20slang%3D%22en-US%22%3EThis%20dos%20not%20work%20for%20dates.%20(format%20DD.MM.YY)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822381%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398327%22%20target%3D%22_blank%22%3E%40ROI_NOIF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20doesn't%20matter%2C%20dates%20in%20Excel%20are%20sequential%20integers%20where%201%20is%2001%20Jan%2C%201900.%20Another%20story%20if%20you%20have%20text%20representation%20of%20dates.%20You%20may%20check%20in%20any%20empty%20cell%20by%20%3DISTEXT(A1).%20If%20it%20return%20false%2C%20you%20have%20dates%2C%20otherwise%20date.%20Other%20way%2C%20Ctrl%2B1%20on%20A1%20and%20apply%20General%20format%20to%20check%20if%20you%20see%20integer%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822385%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398327%22%20target%3D%22_blank%22%3E%40ROI_NOIF%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20check%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822546%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822546%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398327%22%20target%3D%22_blank%22%3E%40ROI_NOIF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20simply%20means%20that%20your%20dates%20are%20not%20real%20dates%20but%20the%20dates%20in%20text%20format.%3C%2FP%3E%3CP%3EExcel%20treats%20dates%20as%20real%20numbers%20and%20to%20check%20this%20enter%20a%20date%20in%20any%20cell%20and%20change%20it's%20format%20to%20General%2C%20the%20entered%20date%20(if%20a%20valid%20date)%20will%20be%20converted%20to%20the%20number%20underneath%20it.%3C%2FP%3E%3CP%3EThe%20other%20way%20to%20check%20if%20a%20date%20in%20any%20cell%20is%20real%20date%20or%20a%20date%20as%20text%2C%20assume%20your%20date%20is%20in%20A2%20then%20place%20the%20formula%20%3DISNUMBER(A2)%20in%20any%20blank%20cell%20and%20if%20the%20formula%20returns%20True%2C%20that%20means%20the%20date%20in%20A2%20is%20a%20real%20date%20otherwise%20it's%20just%20a%20text.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESo%20if%20the%20dates%20in%20column%20A%20and%20B%20are%20not%20the%20real%20dates%2C%20you%20may%20select%20whole%20column%20A%20and%20make%20a%20New%20Rule%20for%20conditional%20formatting%20using%20the%20formula%20given%20below%20and%20set%20the%20format%20as%20per%20your%20choice.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DAND(A1%26lt%3B%26gt%3B%22%22%2CB1%26lt%3B%26gt%3B%22%22%2CIF(ISNUMBER(A1)%2CA1%2CDATE(20%26amp%3BRIGHT(A1%2C2)%2CMID(A1%2C4%2C2)%2CLEFT(A1%2C2)))%26gt%3BIF(ISNUMBER(B1)%2CB1%2CDATE(20%26amp%3BRIGHT(B1%2C2)%2CMID(B1%2C4%2C2)%2CLEFT(B1%2C2))))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20refer%20to%20attached%20with%20a%20conditional%20formatting%20applied%20to%20the%20column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822551%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822551%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20texts%2C%20it's%20more%20reliable%20and%20more%20simple%20to%20convert%20texts%20to%20dates%20-%20Text%20To%20Columns%20and%20set%20proper%20date%20format%20on%20third%20step%20of%20the%20wizard%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823041%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%2C%20Dates%20as%20Text%20entries%20can%20be%20converted%20into%20the%20real%20dates%20using%20the%20Text%20to%20Columns%20feature%20if%20date%20has%20the%20default%20date%20separator%20in%20it%20i.e.%20if%20the%20default%20separator%20for%20the%20date%20in%20the%20regional%20settings%20is%20%22%2F%22%20and%20the%20text%20date%20uses%20the%20%22.%22%20as%20date%20separator%2C%20it%20won't%20be%20converted%20into%20the%20real%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823072%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20502px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128540iDADC7F94C82F119B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20black%20is%20the%20source%2C%20coloured%20is%20Text%20To%20Columns%20result%20(I%20use%20ISO%20date%20format)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823085%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20you%20used%20Text%20to%20Columns%20and%20set%20the%20format%20of%20the%20column%20to%20date%20in%20the%20following%20step.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20talking%20about%20one%20step%20Text%20to%20Columns%20method%20which%20works%20for%20the%20dates%20with%20the%20default%20date%20separator%20but%20entered%20as%20Text%20entries%20and%20in%20that%20method%2C%20you%20just%20need%20to%20select%20the%20date%20column%20--%26gt%3B%20Text%20to%20Column%20and%20click%20on%20Finish%20in%20the%20first%20step%20itself.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823156%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20that's%20with%20third%20step%2C%20as%20I%20said%20in%20on%20of%20previous%20posts%20this%20morning%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823332%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20you%20are%20right%2C%20I%20see%20that%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E
ROI_NOIF
New Contributor

Hello all, 

I have to columns of dates A & B. 

I want to color the cell in column A red if the date is later then the date in the cell in column B in the same row.

need to do this to all rows... 

Thanks

Roi

11 Replies

@ROI_NOIF 

Hi Roi,

 

Assuming your data starts from A1, select column A and apply conditional formatting rule with the formula

=$A1>$B1

 

This dos not work for dates. (format DD.MM.YY)

@ROI_NOIF 

Format doesn't matter, dates in Excel are sequential integers where 1 is 01 Jan, 1900. Another story if you have text representation of dates. You may check in any empty cell by =ISTEXT(A1). If it return false, you have dates, otherwise date. Other way, Ctrl+1 on A1 and apply General format to check if you see integer number.

@ROI_NOIF , you may check attached file

@ROI_NOIF 

 

This simply means that your dates are not real dates but the dates in text format.

Excel treats dates as real numbers and to check this enter a date in any cell and change it's format to General, the entered date (if a valid date) will be converted to the number underneath it.

The other way to check if a date in any cell is real date or a date as text, assume your date is in A2 then place the formula =ISNUMBER(A2) in any blank cell and if the formula returns True, that means the date in A2 is a real date otherwise it's just a text.


So if the dates in column A and B are not the real dates, you may select whole column A and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.

=AND(A1<>"",B1<>"",IF(ISNUMBER(A1),A1,DATE(20&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2)))>IF(ISNUMBER(B1),B1,DATE(20&RIGHT(B1,2),MID(B1,4,2),LEFT(B1,2))))

 

Please refer to attached with a conditional formatting applied to the column A.

 

 

@Subodh_Tiwari_sktneer 

If texts, it's more reliable and more simple to convert texts to dates - Text To Columns and set proper date format on third step of the wizard

@Sergei Baklan 

I think, Dates as Text entries can be converted into the real dates using the Text to Columns feature if date has the default date separator in it i.e. if the default separator for the date in the regional settings is "/" and the text date uses the "." as date separator, it won't be converted into the real date.

@Subodh_Tiwari_sktneer 

It works

image.png

In black is the source, coloured is Text To Columns result (I use ISO date format)

@Sergei Baklan 

 

It seems that you used Text to Columns and set the format of the column to date in the following step.

 

I was talking about one step Text to Columns method which works for the dates with the default date separator but entered as Text entries and in that method, you just need to select the date column --> Text to Column and click on Finish in the first step itself.

@Subodh_Tiwari_sktneer 

Yes, that's with third step, as I said in on of previous posts this morning

@Sergei Baklan 

 

Yes, you are right, I see that now.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies