Restrict number of times date is allowed to generate?

htokay

I am looking to formulate a column to generate a date based on the submission date and turnaround time of the type of task. However, we only want to allow each date to generate up to three times. If that date reaches a fourth date, then we want it to add a day to the generated date.

Example:

1. Doc1 received on 2/24, turnaround time is 4 days, generated due date is 2/28
2. Doc2 received on 2/25, turnaround time is 3 days, generated due date is 2/28
3. Doc3 received on 2/25, turnaround time is 3 days, generated due date is 2/28
4. Doc4 received on 2/26, turnaround time is 2 days, generated due date is 2/28
   1. Since this is the fourth task that pulls the due date of 2/28, we would want this task to add a day to the due date, returning 2/29.
      1. This parameter would apply to the entire column so that it continues as tasks are entered.

Currently, I only have the formula calculating the date (business days) and with an IFERROR to show blank. Here is the starting point:

=IFERROR(WORKDAY([Date Assigned]@row, [Turnaround Time (Days)]@row), "")

I have looked this up and have not found any direction on a formula that would do the above. I do know this is a function in excel and have used that, but I don't believe it is something that is transferrable to Smartsheet.

kowal

hi @htokay,

the only quick around that comes to my mind is that you need to add 3 extra helping columns (with checkboxex fore exampl) named: "date change first", "date change second", "date change third". You will also need to create 3 workflows (you can create one and duplicate) that each time the Date changes it will trigger the workflow to fill the checkbox in this row. Firs time it changes the checkbox will occur in first column: "date change firs"t, if date changes again the workflow will check (condition) if change first is filled so it will fill the change second… and so on. when 3 columns with checkboxes will be filled you can lock the entire row etc.

hope this helps a bit.

Paul Newcome

Is it correct that the 4th date that gets pushed to 3/1 would also count as one of the dates for 3/1. So only 2 more submissions could be made with dates of 3/1 at that point?

htokay

@kowal , thank you for the advice. After further review, we believe my initial parameters would not work as the rows are set to be removed from the sheet once the task is complete. This would then alter the remaining entries and skew the due dates. Thank you for your review on this though, much appreciated!

kowal

always pleasure.