Conditional Formatting comparing two dates

Options

I'd like to create a conditioning formatting to highlight a cell based on the difference between two dates. Column A is the expected date of delivery. Let's say 1/1/23. Column B is the actual date I received it, say 1/5/23. I'd like it to follow this rule:


Highlight Column A cell when:

Received date is more than 5 days before expected date = green highlight

Received date between 5 days before and 5 days after expected date = yellow highlight

Received date is more than 5 days after expected date = red highlight


This was I can track if materials are being delivered in a timely manner.

Best Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @Ksephora You would need to create a helper column to first calculate the days before/after received date. You can then use this column as the condition for the formatting.

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    edited 08/31/23 Answer ✓
    Options

    Hi @Ksephora,

    First, create a helper column to get the amount of days in-between the dates. Use this formula (make it a column formula).

    =[Received Date]@row - [Expected Date]@row

    Next, setup 3 conditional formats.

    Here is what it looks like - make sure to set the formatting on the correct column for your needs.

    Hope this helps,

    Dave

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!