We help IT Professionals succeed at work.

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

617 Views
Last Modified: 2014-01-10
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.
Comment
Watch Question

Commented:
Need we take holidays into account?
jaguar5554Business Analyst

Author

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

Commented:
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.
jaguar5554Business Analyst

Author

Commented:
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? :-)

Commented:
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.
jaguar5554Business Analyst

Author

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

Commented:
No.  I have a partial solution, but there is a bug which I have to mull over at the local tavern.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
jaguar5554Business Analyst

Author

Commented:
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!
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great! You are welcome.

/gustav

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

Author

Commented:
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!
jaguar5554Business Analyst

Author

Commented:
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.
jaguar5554Business Analyst

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.