Possible Solutions, anyone?


Hi Smarties,

One of the users I am working with right now would like to have the following built in Smartsheet.

Request Intake sheet > requester to enter the Task Type, Date Submitted and desired date for completion, and I will build a formula to estimate start date and estimate end date.

I have managed to build a formula to estimate the start date and end date based on the Workday function and holidays and the Task Type Duration.

However, users would also like to indicate when travel is required for the request to be completed (photography session, etc). This means I will create two more columns Travel Start Date and Travel End Date for the row which has a flag of "Travel Required?".

My challenges are

  1. When there is a travel request for a task, the task owner will not be able to complete all other tasks during the travel period. So all other tasks which have an overlapped estimated start date and/or end date during the travel period will need to automatically start after the travel end date (1 day after).
  2. See example below, Task No 2, Type A submitted on 19th Aug and require by 26th Aug. This task usually takes about 2 days to complete. However, because the task owner will need to travel from 23rd Aug to 25th Aug, the end date for Task No 2 is automatically changed to 26th Aug.
  3. Similar to Task 3 Type B, the request was submitted on 16th Aug and required by 27th Aug. However, because the "travel required" is being flagged, it takes the travel dates into consideration and Estimated Start Dates will only start after the travel end date.
  4. The formula for the overlapped column to flag to other tasks with estimated start and/or end dates that fall between the travel period.

I am thinking to build another sheet - call it Travel Dates, in which Task Owner will enter their travel dates and add this to the Workday formula. So any estimated Start Date and End Date falls in the dates in this sheet will be treated like a Public Holiday list.

Any thoughts?



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!