Check Box Advances Due Date to Next Month

Is there way to create a condition where the 'Due Date' advances to the next month when a check box is clicked?

ie: if a monthly task (due date is set to July 15) is completed, the person would click the 'checkbox' cell. This would trigger the 'Due Date' field to advance to August 15.

Best Answer

«1

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi Stacey,

    I think this could be done partially if the date in your due column can be generated by a formula.

    Something like the TODAY function + MONTH in some hidden reference cells. In your due cell you would use an IF formula counting + 1 to MONTH when the checkmark is activated.

    Logic would be: if checkmark = no, then MONTH + day 15

    else; MONTH +1 + day 15

    Your done column needs to be unchecked manually though.


    Have you thought about using scheduled reminders or update requests for this obviously recurring task?

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Thank you, Stefan, for your prompt reply. When I create the formula:

    =IF([checkbox]@row = no, MONTH DAY 15, MONTH+1 DAY 15)

    I get a #UNPARSEABLE. What am I missing? (I am putting the formula in a date field)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you trying to just go to the same day "next month"? What happens to the checkbox when the date changes to next month's date? Are there any updates to the row after the box is checked (aside from the date changing) until it is time to check the box again?

  • What I am looking for is a formula to change the 'due date' to the next month (same date) once the 'completed' check box is clicked. ie: Task is completed on 4/15/2020. The person checks the box and the due date advances to 5/15/2020.

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi Paul, hi Stacey,

    that's why I mentioned in may first post, that the checkbox needs to be unchecked manually. Currently I can think of no easy way within the same sheet to automate everything.

    Stacey, your formula "=IF([checkbox]@row = no, MONTH DAY 15, MONTH+1 DAY 15)" has some mistakes:

    • If your column is named 'checkbox' you don't need the extra brackets
    • A checkbox can only have the value 1 or 0. 'no' ist not possible

    Try something like this:

    =IF(checkbox@row = 1, (MONTH(Datum1) + 1) + "/15th", MONTH(Datum1) + "/15th")

    Carefully look at the code +"/15th" to understand how to change the appearance of the date. You basically combine a formula result with a text block.

    Datum1 is the cell where your TODAY function lives.

    Hope this helps.

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Thank you for your help, Stefan and Paul! I am not real savvy on formulas. Just enough to get by in Excel.

    Are you trying to just go to the same day "next month"? YES!

    What happens to the checkbox when the date changes to next month's date? I will go in and uncheck the box at the end of the month

    Are there any updates to the row after the box is checked (aside from the date changing) until it is time to check the box again? No changes to the row.

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi Stacey,

    if you can do Excel formulas, you most likely can do smartsheet ;-)

    This is Smartsheet help on formulas:

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So what I am going to suggest is (under the assumption that each task is unique)...

    Add the Modified (date) system column.

    Use the "Save as new" feature to create a copy of the sheet then delete all rows from the sheet.

    Then we will go back to your original. The first thing we are going to do is create a Copy Row Automation that automatically copies rows when the Checkbox becomes checked.


    Then we go to your date column and use a formula to pull the most recent date for that particular task being checked off. If that date is greater than or equal to (for example) the 15th of this month, then we will generate the date for the 15th of the next month, but if the last time the box was checked was before the 15th of the current month, then we will leave the date for the 15th of the current month.

    =IF(DATEONLY(MAX(COLLECT({Copy Sheet Modified Column}, {Copy Sheet Task Name Column}, [Task Name]@row))) >= DATE(YEAR(TODAY()), MONTH(TODAY()), 15), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 15), DATE(YEAR(TODAY()) + 1, 1, 15)), DATE(YEAR(TODAY()), MONTH(TODAY()), 15))

  • Paul- that is an amazing formula! I plugged it in as follows and get a #invalid reference:

    =IF(DATEONLY(MAX(COLLECT({Copy Sheet Modified Column}, {Copy Sheet Item}, Item3))) >= DATE(YEAR(TODAY()), MONTH(TODAY()), 15), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 15), DATE(YEAR(TODAY()) + 1, 1, 15)), DATE(YEAR(TODAY()), MONTH(TODAY()), 15))

    'Item3' is the name of the column / row 3

    I am not familiar with {Copy Sheet Modified Column}, {Copy Sheet Item} Should it be referencing a column?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. You should be creating a cross sheet reference that points at the specified column on the copy sheet. To do this, you start typing your formula...

    =IF(DATEONLY(MAX(COLLECT(


    Then when you get to this point, you will click on the link in the helper box that says "Reference another sheet". Select your copy sheet from the list on the left, then click on the column header for the column listed in the sample formula. Next you will click on the blue button in the bottom right corner that says "Insert Reference".


    It should take you back to the sheet you are working in and you should now see...

    =IF(DATEONLY(MAX(COLLECT({Copy Sheet Name Range 1}


    Type your comma and continue entering the formula until you get to the next cross sheet reference (indicated by the curly brackets { } ) and then follow the above steps to create that one.

  • Paul- i am not understanding the existence of the COPY sheet. I like your idea of creating a new line/task when the task is completed (check mark checked) on the same/current sheet (Finance Recurring Task List). That would fit the bill perfectly.

    My formula looks like this but still getting an error #unparseable:

    =IF(DATEONLY(MAX(COLLECT({Finance Recurring Task List COPY Range 2}, {Finance Recurring Task List COPY Range 1},[Item]3))) >= DATE(YEAR(TODAY()), MONTH(TODAY()), 15), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 15), DATE(YEAR(TODAY()) + 1, 1, 15)), DATE(YEAR(TODAY()), MONTH(TODAY()), 15))))))

    Thank you for sharing your knowledge! I am not very savvy with formulas.

    Lorna

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Take the sheet you are working with and "Save as New". Delete all data from this copy. Go back to your original sheet and set up a Copy Row Automation that is triggered when the box becomes checked.


    Then set up your formula using the above steps for creating cross sheet references and reference the copy sheet.


    The box will still need to be manually unchecked once the date is updated through the formula.

  • Well... after all that, my company does not have the Business or Enterprise plan... just Team. I am not able to copy the row :-(

    Thank you so much for all your help. I really appreciate your wisdom (and patience!).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I didn't realize the Automation wouldn't be available to you. My apologies. Let me do some thinking, and I will see if I can come up with something else for you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!