Hyperlinks and Conditional Formatting on Date

SManago

Hello, I know this might be a basic question but since we’re new to Smartsheet, we’d like to kindly ask reason why the conditional formatting to date with hyperlinks does not work? Scenario is, we have conditional formatting to highlight cells with dates of expiry of documents. However, we linked the date to the actual documents, and now it does not work. Can we use formula instead? Thank you for helping out!

Spoonhead

To work around this, you can use a formula to manage the highlighting based on the expiry date without directly involving the hyperlinks:

Create a Helper Column: Add an additional column (let's call it "Expiry Status") that will help determine whether the document is expired.

• Enter a Formula: In the "Expiry Status" column, use a formula to check if the date is past the current date. For example:

=IF([Expiry Date]@row < TODAY(), "Expired", "Valid")

Replace [Expiry Date] with the actual name of your date column.

Set Up Conditional Formatting:

• Go to the Conditional Formatting panel.
• Create a new rule.
• Set the rule to apply to the "Expiry Status" column.
• Choose "is equal to" and set the value to "Expired".
• Choose your desired formatting (e.g., red background).

Hyperlink Your Dates: Continue to use hyperlinks in your date column as needed. The conditional formatting will now be managed by the "Expiry Status" column and will correctly highlight the rows based on the date conditions.

Example Scenario:

Let's say you have the following columns:

• Document Name
• Expiry Date (with hyperlinks)
• Expiry Status (helper column)

Your "Expiry Status" column will contain the formula to check the expiry date:

=IF([Expiry Date]@row < TODAY(), "Expired", "Valid")

Then, set the conditional formatting rule to:

• Apply to the Expiry Status column.
• Format cells where Expiry Status is equal to Expired.

This way, your document hyperlinks remain functional, and the conditional formatting will correctly highlight expired documents.

SManago

Hello! Thank you for this! It's a bit complicated but the way you explained it makes it a lot easier. this works! thanks a lot!