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

Carol Hacker
edited 12/09/19 in Archived 2015 Posts

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
    Tim Meeks ✭✭✭✭✭✭



    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?




  • 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
    Richard Rymill SBP ✭✭✭✭✭✭


    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
    Tim Meeks ✭✭✭✭✭✭
    edited 09/24/15



    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.



    project calc.JPG

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭



    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,


This discussion has been closed.