Forum Discussion

rdshetty's avatar
rdshetty
Copper Contributor
Apr 09, 2024

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...
Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Apr 09, 2024

    rdshetty 

    You can achieve this using conditional formatting in Excel.

    Here is how you can set it up:

    1. Select cell C3.
    2. Go to the "Home" tab on the Excel ribbon.
    3. Click on "Conditional Formatting" in the "Styles" group.
    4. Choose "New Rule" from the dropdown menu.
    5. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
    6. 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.
    1. Click "OK" to apply the conditional formatting.
    2. 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.

     

NikolinoDE's avatar
NikolinoDE
Gold Contributor

rdshetty 

You can achieve this using conditional formatting in Excel.

Here is how you can set it up:

  1. Select cell C3.
  2. Go to the "Home" tab on the Excel ribbon.
  3. Click on "Conditional Formatting" in the "Styles" group.
  4. Choose "New Rule" from the dropdown menu.
  5. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
  6. 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.
  1. Click "OK" to apply the conditional formatting.
  2. 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.

 

rdshetty's avatar
rdshetty
Copper Contributor
Apr 09, 2024
Thanks for your reply. I have entered the formulas as you mentioned. I'm able to change colors on C3 cell, but it doesn't display - "Valid", "Expired", or "Expiring"
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 09, 2024

    rdshetty 

    Conditional formatting in Excel doesn't directly allow for displaying text in a cell based on a condition. Instead, it only formats the appearance of the cell based on the condition.

    To display text based on a condition, you can use a combination of conditional formatting and a formula in an adjacent cell. Here's how you can do it:

    1. Keep your conditional formatting rules as they are to change the cell color based on the conditions.
    2. Use a formula in cell D3 to display "Valid", "Expired", or "Expiring" based on the condition in cell B3.
    3. Enter the following formula in cell D3:

    =IF(B3>TODAY(), "Valid", IF(B3<TODAY(), "Expired", IF(AND(B3>=TODAY(), B3<=TODAY()+30), "Expiring", "")))

    This formula checks if the date in cell B3 is in the future, in the past, or within the next 1 month, and displays the corresponding text in cell D3.

    Make sure to adjust the cell references in the formula if your data is located in different cells. Once you enter this formula in cell D3, it should display "Valid", "Expired", or "Expiring" based on the date entered in cell B3.

    • rdshetty's avatar
      rdshetty
      Copper Contributor
      Apr 09, 2024
      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.
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Apr 09, 2024
        =IF(B3="", "", IF(B3>TODAY(), "Valid", IF(B3<TODAY(), "Expired", IF(AND(B3>=TODAY(), B3<=TODAY()+30), "Expiring", ""))))

Share

Resources