Link to home
Start Free TrialLog in
Avatar of jaguar5554
jaguar5554Flag for United States of America

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.
Avatar of jerryb30
Flag of United States of America image

Need we take holidays into account?
Avatar of jaguar5554


Yes -- my apologies for not including that important detail!
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.
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? :-)
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.
I see. I'm assuming there is no way for the DateSerial function to specify a day v. weekday?
No.  I have a partial solution, but there is a bug which I have to mull over at the local tavern.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
Great! You are welcome.

Just to give credit-I visited the tavern. Did not come up with a solution. Gustav may or may not have visited a tavern.
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!
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.
jerryb30 -- I hope you had an enjoyable visit at the pub.
gustav -- treat yourself to a visit at the pub.