Possible Solutions, anyone?

Syed Muhafzal

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?

Thanks

Paul Newcome

Try something like this...

=IF(COUNTIFS([Travel Start Date]:[Travel Start Date], @cell <= [Estimated Completion Date]@row, [Travel End Date]:[Travel End Date], @cell >= [Estimated Start Date]@row) > 0, 1)

Paul Newcome

What are your current formulas for the start and end dates?

Syed Muhafzal

hi @Paul Newcome

Estimated Start Date : =WORKDAY([Date Submitted]@row, 1, {Holidays})

*I put a one for work to start the next business day no matter what time in the day the request comes in.

Estimated End Date : =DATEONLY(WORKDAY([Estimated Start Date]@row, [Task Type Duration (Days)]@row, [Requester Approval Duration]@row, {Holidays}))

The requester approval duration is a new column I have created for calculating a duration between dates when we send for approval and when the approval has been received (both dates are using "record a date" automation)

Thanks

S

Syed Muhafzal

And the Travel Start Date and End Date are manually entered by the requester through form :)

Syed Muhafzal

@Paul Newcome

Hi Paul - I tried to update the Overlap column with the formula below, but doesn't seem to work. Can you check if there is anything wrong with the formula?

=IF(COUNTIFS([Estimated Start Date]:[Estimated Start Date], [Estimated Start Date]@row, [Estimated Completion Date]:[Estimated Completion Date], [Estimated Completion Date]@row, [Travel Start Date]:[Travel Start Date], @cell >= [Estimated Start Date]@row, [Travel End Date]:[Travel End Date], @cell <= [Estimated Completion Date]@row) > 1, 1)

Thanks

S

Paul Newcome

What is not working about it? Are you getting an error or an incorrect output?

Syed Muhafzal

It is not flagging where I thought it should be flagging. Maybe my formula sequence is wrong? For example, I just put Travel Start Date 20 Aug to 25 Aug as Travel End Date on the first row, ideally, it should flag out 2nd, 3rd and 5th row?

Paul Newcome

Try something like this...

=IF(COUNTIFS([Travel Start Date]:[Travel Start Date], @cell <= [Estimated Completion Date]@row, [Travel End Date]:[Travel End Date], @cell >= [Estimated Start Date]@row) > 0, 1)

Syed Muhafzal

Thanks as always Paul. Yup, this will work - you made it look so easy! I will probably do a bit more research on my other challenges above. Nonetheless, this is great!