asked on

Find 3rd working day of the following month for a given date

I need to calculate three (3) working (or week) days following the last day of the month for a given date. For example, if WEDate = 1/3/14, the result should be 2/5/14 (3rd working (or week) day of the following month. (I'm using the following to return the last day of WEDate's month: LDOM: DateSerial(Year([WEDate]),Month([WEDate])+1,0). Any help is greatly appreciated. Thank you.

jerryb30

Need we take holidays into account?

jaguar5554

ASKER

Yes -- my apologies for not including that important detail!

jerryb30

US Holidays? Any year? As far I can tell tell, only New Year's Day, Independence Day and Labor day could fall within the first week, but with multiple years, you still have a multitude of possible holidays.

jaguar5554

ASKER

US Holidays and current year; that is, current year of the WEDate. Specifically, I only need to see the 3rd working (or week) day of the month following the WEDate being evaluated. Clear as mud? :-)

jerryb30

Not really. A date in December will go over to the next year.
You might need a table of US Holidays by year. Which could be as few as 3 dates or as many as you have years of data times three.

jaguar5554

ASKER

I see. I'm assuming there is no way for the DateSerial function to specify a day v. weekday?

jerryb30

No. I have a partial solution, but there is a bug which I have to mull over at the local tavern.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jaguar5554

ASKER

That, gustav, is a beautiful thing! I should visit the tavern more often myself! LOL

I changed datDateNext = DateAddWorkdays(datDateFrom, 3)
to datDateNext = DateAddWorkdays(datDateFrom, 2) and on intial spot check am getting the exact results I'm looking for. All I need to do is update the tblHoliday table at the beginning of the new year. Genius. Thank you so much!

Gustav Brock

Great! You are welcome.

/gustav

jerryb30

Just to give credit-I visited the tavern. Did not come up with a solution. Gustav may or may not have visited a tavern.

jaguar5554

ASKER

Scratch that! I changed it back to datDateNext = DateAddWorkdays(datDateFrom, 3)
(the data I was testing was entered for the wrong year!!). Your original expression works perfectly. Thank you Thank you again. Expert!

jaguar5554

ASKER

I combed the Internet and none of the solutions I found worked as expected. This solution is flawless. Gustav is an expert and generous in sharing his expertise.

jaguar5554

ASKER

jerryb30 -- I hope you had an enjoyable visit at the pub.
gustav -- treat yourself to a visit at the pub.
:-)