+ Reply to Thread
Results 1 to 7 of 7

How to populate a calendar with a sequence of predetermined days, dependent on start date

  1. #1
    Registered User
    Join Date
    05-03-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    365 Pro Plus
    Posts
    4

    How to populate a calendar with a sequence of predetermined days, dependent on start date

    Hi Everyone,

    I have been trawling the web for assistance with this but it is proving to be well over my head. Here is the background of what I am trying to do.

    I am a medical researcher and, without being modest, we are trying to reduce the negative side effects chemotherapy treatment, whilst improving its cancer killing ability! Pretty cool! But to do that we need to make treatment protocols and at the moment we are doing it by hand (as we arent very excel savvy) and entering all the information into the calendar which could be made more efficient.

    This is what I am trying to do.

    Have a calendar that autopopulates 2 months depending on the start date of the treatment schedule (ie I want to start the protocol on the 5/5/2016, thus May and June would be created)
    Have a question that asks, when would you like to start and what is your treatment protocol (this should be answered as the 5/5/2016 and Days 0, 1, 3, 5, 8, 10, 12, 15 )
    Have a question that asks, how many groups are in the protocol? (this would then create x amount of groups ie 10.) and give a space to name the group.
    From these two questions, the formula should then populate the calendar with group one starting on 5/5 with Group Name 1, Day 0, 6/5 Group name 2 day 0 (then under that cell in the same day) Group name 1 day 1, 7/5 (Group name 3 day 0, group name 2 day) and so on.
    What makes this difficult, is I need to make sure that the protocol final day in the manually detmerined protocol does not land on a saturday or sunday, or land on a pre determined 'holiday'.


    Is this possible? Ive got a spreadsheet now that autocreates one month based on the start date (but i need two) and identifies days which are weekends and manually entered "holidays" as false.

    Any direction would be great, I'm not even sure what to search for for this.

    Thanks a million!

    James

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,151

    Re: How to populate a calendar with a sequence of predetermined days, dependent on start d

    It sounds like it could be done with offsets and / or lookups, however it would help a lot if you could provide a sample workbook with the questions and how they affect the dates and how you would like the workbook to look as a finished product
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-03-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    365 Pro Plus
    Posts
    4

    Re: How to populate a calendar with a sequence of predetermined days, dependent on start d

    Hi dflak,

    Thanks for the reply. I have attached a sample excel sheet as requested.

    I have also attached a two documents that we are using now to give an idea of what the calendar and to do list should look like.

    Thanks again for for your time!

    James

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,151

    Re: How to populate a calendar with a sequence of predetermined days, dependent on start d

    I am afraid that this is a lot more complicated than I originally envisioned. I looked at the word documents and I still can't make the connection between what you fill in on the Protocols details page and what you want to put on the calendar.

    If I start on 6/5/2016 and the treatment days are 1,3,5,8,10 ... Then I have treatments on either 6/5 or 6/6 (is day 1 the day OF the intake or the day AFTER the intake ... I will assume OF). Then the next treatment date is 6/8 and the one after that is 6/11 which is a Saturday. You do not make appointments on weekends and holidays? Do you push it to the nearest workday or the workday after? Finally, this tells us when the appointments are, but not what they are.

    I notice you have a block, "Days to avoid surgeries" how do I tell if an appointment is a surgery? Finally, how do the groups fit into this? I'm assuming that the number of groups can be variable.

    I am bringing all this up because whoever will do this work will need to know these answers. The best I can do with my understanding so far is take the start dates and based on the treatment days, generate a list of treatment dates that exclude weekends, holidays and dates to avoid surgeries.

    Also Excel might not be the right tool to do this. It looks like you will have one spreadsheet per person. If this information were entered into a database, then it would be a lot more flexible. Use the database to manage the data, and then use Excel to report it.

  5. #5
    Registered User
    Join Date
    05-03-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    365 Pro Plus
    Posts
    4

    Re: How to populate a calendar with a sequence of predetermined days, dependent on start d

    Thanks for the reply:

    1) I have altered the new calendar so that day one of treatment will populate as the day OF the date. Thus if there was a 'day 0' it would be entered as Day 1 and hence forth be known as day 1.
    2) There can be appointments on weekends and holidays, however, the protocol can't END on a weekend or holiday. So if the protocol sequence is 1,2,3,5,7,10,15 for example, day 15 can not fall on a weekend or holiday, but all the other days can. If the sequence was 1,3,5,6, 10, 12 for example, 1, 3, 5, 6 and 10 could all land on any day (weekends and holidays inclusive) but day 12 must not land on a holiday or weekend.
    3) Surgeries will always be the last day of the sequence, as denoted in 2). This block, days to avoid surgeries, could also be labeled 'holidays'. As any dates in this block should signify that surgeries cannot land on these dates.

    4) To clarify, weekends and the added 'holidays' or 'dates to avoid surgeries' should never have the last day of the protocol sequence land on them. The last day of any entered protocol sequence will always be a surgery.


    Thanks for the advice, generally speaking there would only ever be one calendar running at any one time. If it makes it any easier, we could lock in the protocol days as 1, 2, 4, 6, 9, 11, 13, 15, 16. Thus making the only variables, the start date, the amount of groups, and the holidays.

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,151

    Re: How to populate a calendar with a sequence of predetermined days, dependent on start d

    The best I can do is come up with a schedule based on what you enter on the protocol details. We have a date issue. My dates are mm/dd/yy, yours are dd/mm/yy - the global settings should take care of this. The only place it may make a difference is in cell E7. You can enter multiple dates separated by a comma. Once again, I think the global settings will take care of the issue.

    Just out of curiosity, Cell E5 is 05/06/16 with my settings what does it show up as with yours? Internally, it's a number so it should come up 06/05/16 for you.

    I had a bit of fun with the formula in the date column for the last date. When I originally used the WORKDAYS function, it put me into June. Then I realized that it was taking out all the weekends between the start date and the number of days to add. So I wound up adding the number of days in Column A to the start date (as if not using WORKDAYS), backing off one day and asking WORKDAYS to give me that date plus 1.

    P.S. The holidays are totally random. I figure you celebrate New Years and Christmas. I have no idea what other holidays you have. Edit this table to reflect reality.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-03-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    365 Pro Plus
    Posts
    4

    Re: How to populate a calendar with a sequence of predetermined days, dependent on start d

    Hi Dflak,

    Thanks for that! Looks great!

    You're right with E5 it does alter to my dd/mm/yy setting which is great! The holidays we can definitely update

    That takes me a step forward, Im watching tutorials on how to populate a calendar based on a schedule. Thanks again for your assistance so far, it's greatly appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Change your Calendar work days and the start of your week Excel 2010
    By jason.hampton in forum Excel General
    Replies: 4
    Last Post: 08-08-2015, 09:58 AM
  2. Replies: 1
    Last Post: 05-26-2014, 03:46 AM
  3. Replies: 2
    Last Post: 03-30-2014, 06:10 PM
  4. Calculate End Date Using Start Date and No. of Days excluding specific days.
    By SinusxCosinusx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2013, 05:48 AM
  5. [SOLVED] How do I Auto Populate a Calendar Series per Start and End Date?
    By Inez15 in forum Excel General
    Replies: 3
    Last Post: 11-13-2012, 04:05 PM
  6. Replies: 0
    Last Post: 07-03-2012, 03:52 PM
  7. Calendar days from start date and workdays
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2010, 10:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1