CONDITIONAL FORMATTING WITH DATES

%3CLINGO-SUB%20id%3D%22lingo-sub-3313312%22%20slang%3D%22en-US%22%3ECONDITIONAL%20FORMATTING%20WITH%20DATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3313312%22%20slang%3D%22en-US%22%3E%3CP%3ELEARNING%20EXCEL%20FOR%20WORK%20AND%20I%20AM%20HAVING%20TROUBLE%20TRYNA%20FORMAT%20FOR%20EXPIRED%20OR%20SOON%20TO%20BE%20EXPIRED%20DATES%20(FROM%2030%2C60%2C90%20DAYS).%20WHAT%20RULES%20CAN%20I%20USE%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3313312%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3314020%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20WITH%20DATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3314020%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1381157%22%20target%3D%22_blank%22%3E%40MzTeeRenee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20like%20this%20perhaps%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20869px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369267i78C3FC379E10B3D7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3314029%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20WITH%20DATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3314029%22%20slang%3D%22en-US%22%3Eyes%2C%20jus%20like%20that.%20it's%20how%20i%20start.%20the%20i%20use%20%3DTODAY()%2B30(DAYS)%20and%20nothing%20happens%20or%20it%20formats%20all%20dates%20erroneously%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3314087%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20WITH%20DATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3314087%22%20slang%3D%22en-US%22%3EI%20assume%20you%20didn't%20actually%20write%20(DAYS)%3F%3CBR%20%2F%3EDate%20formatting%2C%20along%20with%20any%20date-based%20calculation%20goes%20wrong%20if%20the%20date%20is%20a%20text%20string%20rather%20than%20a%20numeric%20date%20(a%20count%20of%20days%20since%20'day%200').%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3314607%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20WITH%20DATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3314607%22%20slang%3D%22en-US%22%3E%3CP%3Ecorrect.%20%3DTODAY()%2B30.%20i%20had%20to%20go%20back%20and%20re-enter%20all%20the%20dates%20as%2005-04-2022%20as%20i%20initially%20entered%20date%20like%20this%2005.04.22.%20i%20then%20went%20CELLS%3B%20FORMAT%3B%20DATE%20to%20change%20the%20entire%20column...still%20not%20quite%20right%20tho...im%20sure%20it's%20something%20simple.%20i%20remember%20something%20about%20a%20date%20rule%20and%20adding%20asterick%20or%20something%20before%20the%20numbers%20which%20may%20change%20how%20the%20data%20is%20read.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3314651%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20WITH%20DATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3314651%22%20slang%3D%22en-US%22%3Ewhat%20is%20the%20best%20way%20to%20enter%20so%20that%20the%20rule%20calculates%20correctly%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

LEARNING EXCEL FOR WORK AND I AM HAVING TROUBLE TRYNA FORMAT FOR EXPIRED OR SOON TO BE EXPIRED DATES (FROM 30,60,90 DAYS). WHAT RULES CAN I USE?

 

5 Replies

@MzTeeRenee 

Something like this perhaps?

image.png

yes, jus like that. it's how i start. the i use =TODAY()+30(DAYS) and nothing happens or it formats all dates erroneously
I assume you didn't actually write (DAYS)?
Date formatting, along with any date-based calculation goes wrong if the date is a text string rather than a numeric date (a count of days since 'day 0').

correct. =TODAY()+30. i had to go back and re-enter all the dates as 05-04-2022 as i initially entered date like this 05.04.22. i then went CELLS; FORMAT; DATE to change the entire column...still not quite right tho...im sure it's something simple. i remember something about a date rule and adding asterick or something before the numbers which may change how the data is read.

what is the best way to enter so that the rule calculates correctly