Formula to Trigger Checkbox if Date Meets Criteria

Hi everyone,

I have set up a formula column for a checkbox that I would like to use to compare against a date field and decide whether check box should be "checked" or left blank.

Essentially I'm going for: IF the Date on the row is greater than X amount of days old (for example lets say 90), then true (box is checked), and if it is not, then false (remain unchecked).

I'm currently trying to use an IF statement to compare against TODAYS DATE minus 90 days however I don't think this is giving the desired result.

Any suggestions would be most helpful,

Thank you.

Tags:

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/16/20 Answer ✓

    Hi @AlexP ,

    Should be a straightforward formula.

    In the checkbox column, try:

    =IF(NETDAYS([Date]@row, today())>90, 1, 0)

    This calculates the number of days between your date and today. If it is more than 90 it returns 1 which checks the box.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/16/20 Answer ✓

    Hi @AlexP ,

    You made me think. Appreciate that. I edited my response above. See if that works.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/16/20 Answer ✓

    Hi @AlexP ,

    Should be a straightforward formula.

    In the checkbox column, try:

    =IF(NETDAYS([Date]@row, today())>90, 1, 0)

    This calculates the number of days between your date and today. If it is more than 90 it returns 1 which checks the box.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thanks for your reply - this is exactly what I've been trying and it does not work as I intuitively think - I think what is happening is the following (example date October 27)

    IF(October 27 < September 16), true, false

    So basically today minus 90 days is September 16 so the two dates are just being compared and in this case will always just be false as October 27 is always greater than September 16.

    Let me know if there are some other options!

    Thanks.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/16/20 Answer ✓

    Hi @AlexP ,

    You made me think. Appreciate that. I edited my response above. See if that works.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thanks @Mark Cronk, that works perfectly - I'll add NETDAYS to the toolbox!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Happy to help. Thanks for using the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.