Check Box Advances Due Date to Next Month

Stacey Dorn

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.

Paul Newcome

I feel like maybe we were over complicating this here...

Do I see a [Completed On] date type column in your screenshot? If I am looking at it correctly, it looks like that is manually entered to record the date the box is checked?

If that is correct, why don't we just do something like...

=IFERROR(DATE(YEAR([Completed On]@row), MONTH([Completed On]@row) + 1, 15), DATE(YEAR([Completed On]@row) + 1, 1, 15))

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

Stacey Dorn

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

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?

Stacey Dorn

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.

Paul Newcome

But then does the box become "unchecked" once the date advances?

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

Stacey Dorn

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

Hi Stacey,

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

This is Smartsheet help on formulas:

https://help.smartsheet.com/functions?dlm=111

Greetings

Stefan

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))

Stacey Dorn

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

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.

Stacey Dorn

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

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.

Stacey Dorn

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

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.