Got a good one for you with dates, my head is pickled trying to work it out.

[TheGlovner]

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!

 

[Jonmo1]

You can use the Workday function..

=WORKDAY(A1,2,$G$2:$G$20)

G2:G20 is a list you create of all your holiday dates.
And add the Monday(for christmas) and Tuesday(for Boxing Day) to the holidays list.

 

[mole999]

maybe some sort of case statement that checks for day of week then allows checks for bank holidays from a list, you know the next two years of the dates that will cause issues so just code them separately, you'll only need to update every January for a whole year of standardised reports.

Don't over think it. its ten odd days of a year, not trying to reach the next millenium