TheGlovner
Board Regular
- Joined
- Jul 11, 2014
- Messages
- 80
So here is the problem.
A report is run on each working day which contains data from two working days previous.
The information we have is the date for that data.
So in order to know the report we need to access we know there is rule to add to the data date along the lines of:
Data Day(Monday) = +2days = Report Day(Wednesday)
Data Day(Tuesday) = +2days = Report Day(Thursday)
Data Day(Wednesday) = +2days = Report Day(Friday)
Data Day(Thursday) = +4days = Report Day(Monday)
Data Day(Friday) = +4days = Report Day(Tuesday)
All good.
Now to confuse matters we obviously need to account for Chirstmas (25/12), Boxing day (26/12 I'm in the UK) and New Years Day (01/01) as the report is also not run on these days and no data exists for these days.
But obviously, to complicate matters further, if Christmas Day falls on a Saturday (which by proxy means that Boxing Day falls on the Sunday) then the two public holidays are moved to the Monday/Tuesday so the next actual working day would be the Wednesday, so now 6 days later than the original Data Date.
If Christmas Day is the Sunday, then it and Boxing Day would move forward by one day, making the Wednesday the next working day, again 6 days.
Same sort of rule for New Year, but it would only ever move on to Tuesday being the next working day.
So my task is to come up with some sort of consistent and concise VBA routine/function to apply the change either to the Data Date to calculate the Report Date, but later I will need to use thre Report Date to calculate the Data Date so need to go the other way.
I suppose ideally it would be a function that returns the number of days based on the input of date and what you wanted calculated from it.
Aaaaaaaaaaaaaaaaaaaaand GO!
A report is run on each working day which contains data from two working days previous.
The information we have is the date for that data.
So in order to know the report we need to access we know there is rule to add to the data date along the lines of:
Data Day(Monday) = +2days = Report Day(Wednesday)
Data Day(Tuesday) = +2days = Report Day(Thursday)
Data Day(Wednesday) = +2days = Report Day(Friday)
Data Day(Thursday) = +4days = Report Day(Monday)
Data Day(Friday) = +4days = Report Day(Tuesday)
All good.
Now to confuse matters we obviously need to account for Chirstmas (25/12), Boxing day (26/12 I'm in the UK) and New Years Day (01/01) as the report is also not run on these days and no data exists for these days.
But obviously, to complicate matters further, if Christmas Day falls on a Saturday (which by proxy means that Boxing Day falls on the Sunday) then the two public holidays are moved to the Monday/Tuesday so the next actual working day would be the Wednesday, so now 6 days later than the original Data Date.
If Christmas Day is the Sunday, then it and Boxing Day would move forward by one day, making the Wednesday the next working day, again 6 days.
Same sort of rule for New Year, but it would only ever move on to Tuesday being the next working day.
So my task is to come up with some sort of consistent and concise VBA routine/function to apply the change either to the Data Date to calculate the Report Date, but later I will need to use thre Report Date to calculate the Data Date so need to go the other way.
I suppose ideally it would be a function that returns the number of days based on the input of date and what you wanted calculated from it.
Aaaaaaaaaaaaaaaaaaaaand GO!