Function to return a date of the nearest same day

[Bezzie]

I need to create a function (in VBA) that will return me a future date that is on the same day as the original date. So in essence I want to predict a future date as close to the same day as the original date as possible while still a workday e.g.

Today is 13 March 2014 and it is a Thursday so function could look something like this

Please Login or Register  to view this content.

Take todays date and add 30 days to it which would take us to 12 April 2014 but this is a Saturday so the nearest Thursday to this date would be 10 April 2014 so the return date should be 10 April 2014. But this is only the first part...

The third parameter is something like +1/-1. If the date calculated falls on a public holiday the date returned should be the the previous workday (for a -1) or the next workday (for a +1). This part will have to use the Workday function of Excel because it can give the previous workday or the next workday and it can take a range of dates (public holidays) that will be excluded.

Hope this makes sense. Any pointers in the right direction?

[Kenneth Hobson]

Did you want to add 30 days or 30 working days or one month or other?

=WorkDay or =WorkDay.Intl should get you close. The code with then need to increment or decrement the days to find the closest Thursday. There is a problem though using one of the built-in functions. That is the case where you want to add or subtract one day for a holiday. I suspect that it gets the next day after holidays.

[Kaper]

Here is formula solution for first part:

Starting date in A1, number of days in B1 in and the formula in C1:


=$A$1+B1-MOD(B1+3;7)+3

now second part:

in D1 your +/-1

in G1:G10 list of bank holidays.

in F1 final result:
=WORKDAY(C1-D1,D1,G1:G10)

Of course you can nest it all into one formula, or - if you wish so - prepare VBA function, using the above algorithm

[Bezzie]

Kenneth: The date I specifically want returned is; What is the date of the closest workday of the same day (say a Friday) in one year (+365 days) but if it's a public holiday what is the workday before (-1) or the workday after (+1) the public holiday. The number of days to add could be anything but I'm wanting 365 days at this stage. The day could be any weekday (working days).

Kaper: I'm going to look into your suggestions.

[Bezzie]

Got it working. Thanks Kaper, your suggestion is super.