Forum Discussion
Solved
Conditional formatting for date validity
I'm working with dates in Excel and would like to create an automated system for indicating their validity. Ideally, when a date is entered in cell B3, I'd like cell C3 to display "Valid" if the date...
You can achieve this using conditional formatting in Excel.
Here is how you can set it up:
- Select cell C3.
- Go to the "Home" tab on the Excel ribbon.
- Click on "Conditional Formatting" in the "Styles" group.
- Choose "New Rule" from the dropdown menu.
- In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
- Enter the following formulas for each condition:
- For "Valid" (date is in the future):
- Formula: =B3>TODAY()
- Format: Choose a color or style to indicate validity.
- For "Expired" (date is in the past):
- Formula: =B3<TODAY()
- Format: Choose a color or style to indicate expiration.
- For "Expiring" (date is within the next 1 month):
- Formula: =AND(B3>=TODAY(), B3<=TODAY()+30)
- Format: Choose a color or style to indicate expiration.
- Click "OK" to apply the conditional formatting.
- Click "OK" again to close the Conditional Formatting Rules Manager.
Now, whenever you enter a date in cell B3, cell C3 will display "Valid" if the date is in the future, "Expired" if it's in the past, and "Expiring" if it's expiring within the next 1 month. The formatting will update automatically based on the entered date. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Thank you so much for this. Now when i copy and paste this formula to other cells in column C, it automatically displays "Expired" even if column B is empty. Is there a way that cells in column C should display "Valid" or "Expired" only when date is entered in column B, otherwise it should be blank.
This is gradually growing in scope. A couple of ideas.
1. My original formula using IFS can be easily extended to
= LET(
days, expiry - TODAY(),
IFS(expiry="", "", days<0, "Expired", days<31, "Expiring", 1, "Valid")
)2. Another point is that number formatting can display just about anything in a cell and use a coloured text. To get coloured fills requires conditional formatting but conditional formatting itself can also determine the number formatting to be used. In Excel, what you see is not necessarily remotely similar to what you've got!
[Spreadsheets were invented for end-user computing in the days when WYSIWYG was a thing, but here it is definitely a case of WYSI≠WYG]
"Worksheet formula"
= IF(Expiry, SIGN(QUOTIENT(Expiry - TODAY(), 30)), "")
"Number format"
[Color10]"Valid";[Red]"Expired";"Expiring";ps I think, by chance, this no longer requires 365!
- Thank you for your time and effort
