Complex Date Rx for a Schedule - Need Guidance

Carol Hacker

I have about 60 events, each with a Start Date and an End Date.  Each event is either 1-day, 2-day, 3-day, or 5-day in duration.   Depending on where the event is located it is to be replicated every 2 months, every 3 months or up to every 6 months.   I only want the event to happen Mon-Fri and I don't want it to occur on a holiday (Canadian ones).   I'm trying to do this in Excel and the Rx is beyond me however someone suggested that I could possibly do this direct in Smartsheet.  Right now we upload the final schedule of events into Smartsheet and print in calendar format and it's a thing of beauty and ease to work with.   The issue right now is how to calculate the dates that I need - here's an example:

Event        Start Date          End Date

ABCD        Sep 14, 2015    Sep 16, 2015

This ABCD event is to be scheduled every two months, it is 3-days long.   So is there a Rx that will tell me what days this will occur in Nov 2015, Jan 2016, Mar 2016, May 2016 - want to schedule events until April 2017.

The above could be a 2-day event and could be scheduled every 4 months.   I have about 65 different events.

Any guidance would be much appreciated.   Thank you.

Tim Meeks

Carol,

 

To clarify, each event can either be 1, 2, 3 or 5 days.  All events must be on M-F, that is no event can occur on Sat or Sun.

Each event regardless of the # of days duration may occur every 2, 3, 4, 5 or 6 months?

And not on Candian hoidays?

 

Do I have this correct?

 

thanks,

Tim

Carol Hacker

Event1 is always a specific number of days - each event is always the same number of days, I have multiple events.  Event1 is scheduled in for example Toronto and Ottawa.   In Toronto it is always scheduled every 2 months.  In Ottawa it is always scheduled every 5 months.   The events must fall in Monday to Friday (networkdays in Excel) and Canadian holidays must be avoided as it is a schedule for Canadians working here.

Event1  Toronto  3-day  every 2 months  28-Oct-15  30-oct-15

Event1  Ottawa  3-day  every 5 months  21-Sep-15  23-Sep-15

Event3 Toronto 2-day  every 3 months  2-Nov-15   3-Nov-15

Event3 Ottawa 2-day  every 4 months   12-Nov-15  13-Nov-15

Let me know if this is clearer.

 

Richard Rymill SBP

Carol

You can do this using a fairly complex formula and it sounds like Tim Meeks is on the case for you and I can definitely tell you Smartsheet is the right tool for what you are doing, so enjoy the innovation. 

We have  Excel/Smartsheet conversion specialists on our team who can help with this if not quickly resolved. Just let me know.

Regards RichardR

 

Tim Meeks

Carol,

 

I discussed this issue with one of my Excel guru associates and he estimated it would probably take 1-2 complex formulas and several hours to figure out and implement.

 

I would take a different approach. I would use the Project Scheduling function within SS and setup up multiple projects with the set duration based on what you stated above. (see the screen shot below). I did the below in less than 10 minutes. Once you get the structure setup, it would probably take you less and an hour or two.

 

you can use the project settings to specifiy non-working days to cover your weekends and holidays that the events are NOT to occur.

 

You can then create project 1 and set a predecessor for Project 2 that must follow X days later.  the predecessor would read something like

Project 1 FS+30d assuming Project 2 was to be 30 days later.

 

Then main manual part would be viewing the Gantt chart and seeing if a project covers a weekend. You would then just need to change your lag +/- the # of days to have the project not start or finish on a weekend.

 

Send me an email to tim.meeks AT gmail.com and I can share the sheet I created with you and help you further.

 

Tim 

Brett Evans

Carol,

 

I shared some code with Tim which will help the manual process he is suggesting.  Once he integrates it into the solution above it should help.

 

Happy Sheets,

Brett