Adjusting trip start/end date based on certain members holidays

rissa

Hi everybody, 

 

I'm new to smartsheets and I'm not sure if there's a name for what I'm looking to do.

 

I volunteer for a small afforestation project in my local area. And want to make planning our planting trips a bit easier, as all our members are college students with varying schedules and holidays planned. (I already have it working to just print out available / unavailable)

But there are 2/3 members of the group that need to be present or we have to move the trip(the ones who own cars)

 

Referenced Sheet: Name, Vacation duration, Start Date, End Date, details

Current Sheet: Trip No, Location, Duration, Trip Start Date, Trip End Date, Attending(This is a contact list which allows multiple values but could be separated line by line if necessary)

Basically I was wondering if there is a way to do something like this in smart sheet or should I try another application for organizing, planning, project managing.

 

Basically I'm trying to do something like this

IF {Name Range} = [Name]1 AND (({Start date Range} >= [Trip Start Date]1) OR ({End Date Range} <= [Trip End Date]1) OR ({Start date Range} <= [Trip Start Date]1) OR ({End Date Range} >= [Trip End Date]1)) [if any vacations fall on/within planned planting trip dates basically]

THEN

{The duration of their vacation} + [Possible Start Date]

will output in a column called [Actual Start Date]

 

Is this possible in smartsheet? or am I better off using another application?

 

Any help is appreciated,

Thanks in advance,

Rissa

Paul Newcome

If I am reading your post correctly, it is possible to accomplish what you are looking for. Would you be able to share some screenshots with manual entries of what you are trying to automate?

rissa

Hi Paul,

Thanks for replying,

 

The first sheet is my vacation tracker (with the three core members on it)

and the second sheet is my trip planner.

 

I'm checking if the members vacation dates fall within the possible date parameter and then marking the member as available or unavailable

=IF(OR(COUNTIFS({Member Holiday Sheet Range}, [Attending Members]1, {Member Holiday Sheet Start}, >=[Possible Start Date]1, {Member Holiday Sheet End}, <=[Possible End Date]1) > 0, COUNTIFS({Member Holiday Sheet Range}, [Attending Members]1, {Member Holiday Sheet Start}, <=[Possible Start Date]1, {Member Holiday Sheet End}, >=[Possible End Date]1) > 0), "Unavailable", "Available")

Member Holiday Sheet Range - [details] column on vacation tracker sheet

Member Holiday Sheet Start - [possible start date] column on vacation tracker sheet

Member Holiday Sheet End - [possible end date] column on vacation tracker sheet

To clarify I'm looking to do the following: If a member is unavailable(i.e a core member is on vacation during the possible trip dates) it will return that trip duration so i can add it to my possible start date and return it in the column [actual start date]

 

rissa

I know it's flawed(probably better to add the vacation duration to the possible end date).

Sorry I'm still getting used to this. 

rissa

So I've managed to pull a date based on the resource but it just pulls the first vacation duration connected to that member name and not the one that falls within the dates.

 

This is what I'm using:

=VLOOKUP(Resource7, {Holiday Test Sheet join}, IF(OR(COUNTIFS({Holiday Test Sheet Range}, Resource7, {Holiday Test Sheet Start}, >=[Start Date]7, {Holiday Test Sheet End}, <=[End Date]7) > 0, COUNTIFS({Holiday Test Sheet Range}, Resource7, {Holiday Test Sheet Start}, <=[Start Date]7, {Holiday Test Sheet End}, >=[End Date]7) > 0), 2, 3), false)

Paul Newcome

So basically you need to move the start date to after the end of the person's vacation and maintain the same duration for the trip?

rissa

Yes. The main problem now is I can't get the right duration to print. It just gives back the first trip duration connected to the members name. 

I figured out the rest of it.

Now it's a case of pulling the right duration from the member vacation sheet to the trip sheet so I can use it in my new date calculation.

Paul Newcome

So to make sure I am clear...

 

You want the Wicklow Mountains trip Actual Start Date to move to 4/21/19 (when Sarah returns) and maintain the 3 day trip duration? But the Malahide trip dates would remain the same because Clara's holiday doesn't overlap the trip dates?

rissa

Yes sorry again I know I'm terrible at explaining myself. 

Also was hoping to link the duration of her holiday to the trip sheet in a hidden colum just so we have a rough idea what dates shes gone as sometimes we pull dates forward as we're trying to pick the days that best suit everyone.

Paul Newcome

I have a few ideas. Are you able to share the sheets to me or post published links? It would be much easier to show than to explain on here. It will also allow for faster tweaking and more direct communication.

rissa

Yes I can share them with you.

What email address will I share them to?

Paul Newcome

To anyone who may have been following this post:

.

Rissa shared both sheets to me. We were able to use cross sheet references in a JOIN/COLLECT formula to pull any vacation dates that overlapped the trip date. We were able to use multiple sets of criteria with the COLLECT function to narrow down what was pulled into a date column. We repeated the formula (with a minor tweak) to pull both the start and end dates of the vacation. The criteria we used were...

.

1. Name

2. Vacation start date LESS THAN trip end date.

3. Vacation end date GREATER THAN trip start date.

.

The only thing we had to change was the cross sheet reference range. One for Start Date; one for finish.

.

If there were no dates that met the criteria, then the trip could stay as scheduled. If there were dates, then it needed pushed out. To do this, we simply automated the [Available/Unavailable] column with a basic IF statement saying that if there is a date, put "Unavailable", otherwise put "Available".

.

We then used some basic functions to take the Possible Start Date and (if there was an overlap) move it to the day after the vacation end date and add the corresponding duration. We did not use an actual duration column for this though because a trip that only took one day would display 1 in the duration column. If we added that to the new trip start date, then it would show an end date of the next day as opposed to the same day.

.

To get around this we used a formula similar to the one below...

 

=[Actual Start Date]@row + ([Possible End Date]@row - [Possible Start Date]@row)

.

This way a 1 day trip would add 0 to the Actual Start Date and reflect the correct Actual End Date (same day).