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

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

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.

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.