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]),
Microsoft AccessMicrosoft ApplicationsMicrosoft Office
Last Comment
jaguar5554
Need we take holidays into account?
ASKER
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.
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? :-)
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That, gustav, is a beautiful thing! I should visit the tavern more often myself! LOL
I changed datDateNext = DateAddWorkdays(datDateFro
to datDateNext = DateAddWorkdays(datDateFro
I changed datDateNext = DateAddWorkdays(datDateFro
to datDateNext = DateAddWorkdays(datDateFro
Great! You are welcome.
/gustav
/gustav
Just to give credit-I visited the tavern. Did not come up with a solution. Gustav may or may not have visited a tavern.
ASKER
Scratch that! I changed it back to datDateNext = DateAddWorkdays(datDateFro
(the data I was testing was entered for the wrong year!!). Your original expression works perfectly. Thank you Thank you again. Expert!
(the data I was testing was entered for the wrong year!!). Your original expression works perfectly. Thank you Thank you again. Expert!
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.
ASKER
jerryb30 -- I hope you had an enjoyable visit at the pub.
gustav -- treat yourself to a visit at the pub.
:-)
gustav -- treat yourself to a visit at the pub.
:-)