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

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!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,914
Messages
5,598,840
Members
414,261
Latest member
KatieBsc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top