Hello gurus,
Description:
A) I have 8 fields: "start1", "start2"...."start8", each of which has a start date in this format "dd/mm/yyyy". Let's call these fields 'starting dates'. The starting dates are normally always on a Monday. These starting dates represent the date a program begins.
B) I also have a field called "weeks 1", "weeks 2"..."weeks 8". This field is automatically populated and contains numerical variables corresponding to each of these starting dates, and lists the number of "business/school weeks forward" for each of these starting dates (in other words, the duration of the program). Each "week" represents a full business/school week, so "Mon-Fri" is one week. For Instance, "16 weeks" means "sixteen Mon-Fri cycles" mapped over the normal calendar days. The number of weeks varies significantly and is independent. In other words, for field "start1", I have a corresponding numerical field called "weeks 1" that may list "16" weeks. The same goes for all other starting dates, i.e. "start2" has a corresponding field called "weeks 2" which may list "47" (weeks), etc.
C) There is another field called "intensity 1" corresponding to each group of information [that is "start1", "weeks 1", and "end1"], and "intensity1" may be "intensive" or "regular".
D) I need to calculate the corresponding 'ending dates' in a third field respective to each 'starting date'. Let's call each of these fields "end1", "end2"..."end8". I am trying to add the date found in "start1" to the number of weeks in "weeks 1". This has become a headache, for the following reasons, which I cannot tackle one by one for the life of me:
i) The "end1" field date may normally only be on a Friday. However:
but if this Friday happens to be a holiday date, then it must: either fall back to a Thursday if the intensity of the program (which is found in another field called "intensity") is "regular", or go forward to a Saturday if the intensity is "intensive" (also known from the field called "intensity") . Perhaps this can be solved by counting 5-day week cycles for "intensive" and if the holiday is on the naturally ending Friday, fall back on a Thursday, but I do not know how.
ii) Some ending dates must end on a Saturday instead of a Friday, but again this is known based on the intensity of a program found in another field (also automatically populated), i.e. if a program is "intensive", it falls on a Saturday, and if a program is "regular", it falls on a Friday). If this Saturday happens to be a holiday date, then the "end1" field must fall back to the Friday. Perhaps this can be solved by counting 6-day week cycles for "intensive" and if the holiday is on the naturally-ending Saturday, fall back on a Friday, but I do not know how.
So, basically the code here should be: if "the intensity field lists "regular", then the "end" field calculates in 5-day cycles, otherwise if the intensity field lists "intensive", then the "end" field calculates in 6-day cycles. So far, so good. Now the holidays...
iii) Any holiday dates can be taken into account and skipped using an array of all holidays for the next 10 years (which I already have) in the format dd/mm/yyyy:
- Single-day Holidays, i.e. Statutory single-day holidays, must be ignored in the calculation of the duration of total number of weeks, but must be included. For instance if Thanksgiving falls on a Thursday, there simply won't be any classes on that day, but the "business/school week" should still be counted as "one week", regardless.
- Range-day Holidays, i.e. Christmas Holidays, cannot be ignored in the calculation of the duration of total number of weeks, but must be skipped ahead. For instance there are two business weeks from Dec 22nd (or nearest Friday) - when schools close until Jan 8th (or nearest Monday) - when schools resume. I do not know how to add this as a range. It can be circumvented with the cumbersome labour of adding the day-date of each and every single holiday range, one by one, in the array, and the same is true during Spring Break, etc. For example, if finding the necessary code for a dates-exclusion-range is a problem, this can be circumvented by manually inserting one by one these dates in the array, i.e. all days of holidays for 2017, all days of holidays for 2018, all days for 2019, etc., whether these are ranges or single days. It's only about 17-20 days per year, at most. A friend told me that I could use an excel spreadsheet with all dates, i.e. from 2017 until 2027, and then manually mark out the holiday dates (should not take more than a few minutes), and then somehow import this data meaningfully into a javascript valication/calculation code to be used as an exclusion array, but i am clueless as to how to do this.
iv) The starting date in sequence that follows an ending date, i.e. "start2" after "end1", must be on the first Monday following the "end1" date. If that Monday is a holiday, then it must start on a Tuesday, or if there is a holiday range starting on that Monday, i.e. Christmas school vacation, then it must start on the next available non-holiday Monday. For example, "start2" must be on the Monday following the date found in "end1", and must have the same exclusions regarding holidays listed in Section "D", here regarding the "end" dates.
E) lastly, a fourth field, let's call it "final_end_date" must look at "end8", and if end8 is empty, it must then look at "end7", and if end7 is empty, it must look at "end6", and so forth, until it finds one of the "end8-1" fields that lists a date. Then the "final_end_date" must simply display this date.
Obviously, the problem is Section D above. I know there are hundreds of other people with this problem in variations by scanning through the forums and no one seems to adequately address it. I have written this very detailed description because the code necessary to solve it can be written in sections and then compiled, so hopefully people can collaborate.
Here is an example of how it should work when it works.
If I have a starting date of (dd/mm/yyyy) Mon 02/10/2017 in the "start1" field (the only starting date that is manually entered), and in the field "weeks 1" I have the number "12" (meaning 12 cycles of Mon-Fri), then I need to convert the 12 weeks to twelve Mon-Fri cycles starting from 02/10/2017 and map this range across calendar days to find the "end1" date (including/not skipping statutory holidays and excluding/skipping range-holidays in the process). This should mean that the field "end1" automatically populates Fri 22/12/2017 (provided the intensity field is "regular", otherwise it should display Sat 23/12/2017 if the intensity field is "intensive").
Alternatively, if I have a starting date of Mon 16/10/2017 in "start1", and I add 12 business weeks from "weeks 1", then this takes me right through the Christmas break, so the "end1" date should be Fri 19/01/2018, instead (assuming the intensity field is "regular", otherwise it should be Sat 20/01/2018). If that Friday happens to be a statutory holiday, then "end1" needs to display Thu 18/01/2018 (or in the case of an "intensive program ending on a Saturday, if the Saturday happens to be a statutory holiday, the "end1" needs to display Fri 19/01/2018). Only if the Thursday is also a holiday, should it move to the Saturday. If the Saturday is also a holiday, then we have a "holiday range", so we count "half" a week (Mon-Tue-Wed), and resume on the next available non-holiday Monday to finish the program on a Tuesday (counting another "half week) for Mon-Tue) -- essentially either 2 or 3 business days should count as a "half' and 4 or 5 business days should cunt as a "full" week. In turn, "start2" is a Monday following the aforementioned Saturday, Friday or Thursday "end1" date, and this is namely Mon 22/01/2018 (and, again, if this Monday is a statutory holiday, the "start2" date should move to the Tuesday, and if there is a range of holiday dates during that week, it should moves to the next available non-holiday Monday). Then the data cycle repeats, because "start2" has a corresponding "weeks 2" field with the number of respective weeks for the program duration, and "end2" is calculated, and subsequently "start3" is calculated, etc., in like manner.
The non-elegant way of doing all this is to write down all the dates manually and add them to drop menus. it is laborious, and ugly. Who is the coding god on this forum?
Thanks in advance
1 Correct answer
5
Replies
AdChoices