Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Complex Date Rx for a Schedule - Need Guidance
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.
Comments
-
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
-
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.
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives