Rolling Date based upon checkbox??

I have a spreadsheet with a Date Column that is populated weekly. If we get to that date (or even better, 1 day before) and the checkbox in another column is still left unchecked can I get the Date Column to automatically increase by 1 day. I need this action to continue until the checkbox is checked. Thanks!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How is the date column currently being populated? Are you able to provide a screenshot for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • The date column is set up as a Date field that is manually inputted. The "Ready to Inspect" button triggers a report to be generated containing only lines with a date of today + future and ready to inspect checked. What happens though as the inspection date passes without ever being check-marked, those lines fall into a gray zone. The master spreadsheet will end up being thousands of lines long so manually checking for these straggler lines will become cumbersome. Instead I'm hoping to force any date that is today or in the past and does not have the checkmark to automatically roll to tomorrows date. The action will end once Ready to Inspect is checked and the line ends up on the generated Report. Thanks!

  • The logic I was attempting (and failing) was to use an IF/And formula. I tried nesting in a todays date plus 1 but have only returned errors.

    If: Inspection date is not in the future and Ready to Inspect is not checked then return value of todays date + 1

    I've added helper rows that can be hidden.

    Hope this helps! Thanks again!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You won't be able to use a formula and manual entry within the same cell. You would have to have one or the other. Is there some kind of logic that can be applied to potentially use a formula to output the original date? IF so, we can write a formula to fully automate that column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ignoring the manually inputted date field (because I can't remove that from the spreadsheet) and assuming I use the "ghost column" I've just added, what would the formula be? I have double checked spacing and parenthesis and tried using "false" vs "0" as the value for the uncheck box....I just can't get value to return other than Unparseable.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to paste in the formula that is giving you the error?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com