By Richard Rost 7 months ago
Calculate Work Days Between Two Dates
In this video, I'm going to show you how to calculate the number of work days (business days) between two dates in Microsoft Access. Excel has a NETWORKDAYS function. Unfortunately, Access does not. So, using a little VBA we will see how to write our own function.
Anton from Buena Park, California (a Platinum Member) asks: I am in the process of switching everything over from Excel to Access. One function that it seems like Access doesn't have is Excel's NETWORKDAYS function. I use that a lot for calculating the number of business days between two dates. How can I do this in Access?
There is a tiny bug in the code. Make sure you change the first line of the while loop to:
That's all. It just wasn't counting the last day, and I didn't test it fully in the video. The date I checked with ended on a Sunday, which is why I missed it. Thanks to Garry Smith for catching this, and to moderator Kevin Robertson for posting a quick fix.
Members will learn how to exclude holidays and any company-specific days off as well. We will program static holidays (New Year's Eve, Christmas, dates that don't move) into our function, and we will make a table for "floating" holidays and company-specific days off.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the database template from class plus get access to the Code Vault. If you signed up on YouTube you have to register on my YouTube Find User page so I can set up your account here on my web site. If you're not a member, Join Today!
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, workdays, work days, business days, exclude weekends, holidays, net work days, microsoft access networkdays, Convert Networkdays Formula to Access, NETWORKDAYS for Access, Networkdays formula in MS Access, Custom VBA Excel Access Networkdays, Calculate the number of work days
You may want to read these articles from the 599CD News: