Date Occuring

%3CLINGO-SUB%20id%3D%22lingo-sub-3363633%22%20slang%3D%22en-US%22%3EDate%20Occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363633%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20really%20simple%20question%20I%20think....%20I%20simply%20want%20a%20cell%20to%20highlight%20when%20a%20date%20has%20occured%20and%2For%20passed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20%3DB2-A1%26lt%3B%3DToday%20(using%20a%20column%20with%20%3DToday()%20and%20a%20column%20with%20a%20date%20in%20the%20future%20i%20have%20input....%20this%20hasn't%20worked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20%3DDUE%20and%20%3DOVERDUE....%20no%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20have%20another%20rule%20running%20so%20blank%20cells%20are%20conditionally%20formatted%20to%20be%20not%20highlighted%20and%20the%20%22stop%20if%20true%22%20box%20is%20ticked%20for%20this....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20dr%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3363633%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363693%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388112%22%20target%3D%22_blank%22%3E%40Smithla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20cells%20with%20the%20dates%20to%20be%20highlighted.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3E(Or%20edit%20the%20existing%2C%20non-working%20rule)%3C%2FP%3E%0A%3CP%3ESelect%20'Format%20only%20cells%20that%20contain'.%3C%2FP%3E%0A%3CP%3ELeave%20the%20first%20drop-down%20set%20to%20'Cell%20Value'.%3C%2FP%3E%0A%3CP%3ESelect%20'less%20than%20or%20equal%20to'%20from%20the%20second%20drop-down.%3C%2FP%3E%0A%3CP%3EEnter%20%3DTODAY()%20in%20the%20box%20next%20to%20it.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3ESpecify%20the%20desired%20formatting.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Manage%20Rules%2C%20move%20this%20rule%20down%20to%20below%20the%20row%20for%20empty%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363863%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363863%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20response!%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%20it%20still%20hasn't%20worked%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363961%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Occuring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388112%22%20target%3D%22_blank%22%3E%40Smithla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20(without%20sensitive%20data)%2C%20or%20if%20that%20is%20not%20possible%2C%20make%20it%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All

 

I have a really simple question I think.... I simply want a cell to highlight when a date has occured and/or passed.

 

I have tried =B2-A1<=Today (using a column with =Today() and a column with a date in the future i have input.... this hasn't worked.

 

I have tried =DUE and =OVERDUE.... no luck.

 

I also have another rule running so blank cells are conditionally formatted to be not highlighted and the "stop if true" box is ticked for this....

 

Its dr

3 Replies

@Smithla 

Select the cells with the dates to be highlighted.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

(Or edit the existing, non-working rule)

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'less than or equal to' from the second drop-down.

Enter =TODAY() in the box next to it.

Click Format...

Specify the desired formatting.

Click OK, then click OK again.

 

In Manage Rules, move this rule down to below the row for empty cells.

Hi

Thanks for your response!

However it still hasn't worked

@Smithla 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.