By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,677 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,677 IT Pros & Developers. It's quick & easy.

How to convert Excel Formula for Access 2003

P: 10
I am not a programmer - so I don't know code, however, I do need some help if anyone can oblige me...

I have a project that requires me to add 4 workdays (excluding holidays) to a beginning date for numerous employee entries.

The Workday formula in Excel is perfect for what I need, but how can I make it work in Access 2003?
Nov 4 '10 #1

✓ answered by ADezii

What you are requesting involves similar concepts and logic. The general idea is to analyze each Day between [Date Results Received] and [Date Results Due] inclusive of the later Date. If a Day is not a Weekend and not a Holiday, it is added to an Aggregate Total. When all Days have been processed, this Total is then returned by the Function. To the Function you pass the Received and Due Dates, and it will do the rest. I basically through this Code together since I am rather pressed for time, so it is probably not optimized, but it appears functional. I attached a second Demo for your review. Let me know if this works for you, Sabrina.
P.S. - I'll post the Function for reference even though I included an Attachment. Again, this all assumes that the Date Fields are Required, and cannot be NULL.
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcWorkdaysDiff(dteReceived As Date, dteDue As Date) As Integer
  2. Dim intDayCtr As Integer
  3. Dim dteTheDate As Date
  4. Dim intWkDay As Integer
  5.  
  6. If dteReceived = dteDue Then
  7.   fCalcWorkdaysDiff = 0
  8. ElseIf dteReceived > dteDue Then
  9.   For intDayCtr = 1 To DateDiff("d", dteDue, dteReceived)
  10.     dteTheDate = DateAdd("d", intDayCtr, dteDue)
  11.       'If dteTheDate is a Weekday AND NOT a Holiday as listed in the [Holiday] Field of tblHolidays
  12.       If Not (Weekday(dteTheDate) Mod 7 < 2) And _
  13.               DCount("*", "tblHolidays", "[Holiday] = #" & dteTheDate & "#") = 0 Then
  14.                 intWkDay = intWkDay + 1
  15.       End If
  16.   Next
  17.     fCalcWorkdaysDiff = intWkDay
  18. Else        'dteReceived < dteDue
  19.   For intDayCtr = 1 To DateDiff("d", dteReceived, dteDue)
  20.     dteTheDate = DateAdd("d", intDayCtr, dteReceived)
  21.       'If dteTheDate is a Weekday AND NOT a Holiday as listed in the [Holiday] Field of tblHolidays
  22.       If Not (Weekday(dteTheDate) Mod 7 < 2) And _
  23.               DCount("*", "tblHolidays", "[Holiday] = #" & dteTheDate & "#") = 0 Then
  24.                 intWkDay = intWkDay + 1
  25.       End If
  26.   Next
  27.     fCalcWorkdaysDiff = intWkDay
  28. End If
  29. End Function

Share this Question
Share on Google+
21 Replies


ADezii
Expert 5K+
P: 8,675
Sabrina, this functionality does not exist in Microsoft Access as an Intrinsic Function. When I get a chance, or unless someone else provides an answer themselves, I'll create a Custom Function for you that will calculate the Number of Workdays, either plus or minus, from a Start Date. Weekends and Holidays would be excluded, of course. Is this what you are looking for?
Nov 4 '10 #2

P: 10
ADezii, I would be MOST GREATFUL if you could creat a Custom Function for me when you get a chance.

What I really need is to be able to type in a beginning date, add 4 days to that date with the results being an end date calculated minus weekends and holidays.

There will also be some entries that will need to have 2 days added to another beginning date (tracking another kind of category).

Anything you can do to help me would be wonderful! Thank you!
Nov 5 '10 #3

ADezii
Expert 5K+
P: 8,675
Here it is, Sabrina. I created a Custom Function for you that you allow you to add any Number of Work Days to a Start Date. A Work Day would be defined as any Day that is NOT a Weekend and is also NOT a Holiday as defined by pre-assigned Dates in a Table named tblHolidays. I saw no need to complicate matter and allow for Dates prior to a Start Date. Follow the instructions outlined below carefully, if you are still having problems, let me know.
  1. Create a Table named tblHolidays. This Table will consist of a Single Field named Holiday, it will be a Date/Time Data Type, and will be the Primary Key.
  2. Populate this Table with a list of Holidays, or any Day for that matter that you would not wish to designate as a Workday. Below is tblHoliday with some sample Dates that I used:
    Expand|Select|Wrap|Line Numbers
    1. Holiday
    2. 12/24/2010
    3. 12/25/2010
    4. 1/1/2011
  3. Copy-N-Paste the following Function to a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcWorkDays(dteStartDate As Date, lngDays As Long) As Date
    2. Dim intDayCtr As Integer
    3. Dim intWkDay As Integer
    4. Dim dteTheDate As Date
    5.  
    6. If lngDays <= 0 Then Exit Function
    7.  
    8. intDayCtr = 0       'Initialize
    9. intWkDay = 0
    10.  
    11. Do
    12.   intDayCtr = intDayCtr + 1
    13.     dteTheDate = DateAdd("d", intDayCtr, dteStartDate)      'Increment Start Date
    14.       'If dteTheDate is a Weekday AND NOT a Holiday as listed in the [Holiday] Field of tblHolidays
    15.       If Not (Weekday(dteTheDate) Mod 7 < 2) And _
    16.               DCount("*", "tblHolidays", "[Holiday] = #" & dteTheDate & "#") = 0 Then
    17.           intWkDay = intWkDay + 1
    18.       End If
    19. Loop Until intWkDay = lngDays
    20.  
    21. fCalcWorkDays = dteTheDate
    22. End Function
  4. Call the Function in some manner, passing to it a Start Date, and the Number of Workdays from the Start Date, as in the example below. In the example, I want the 7th Workday from 12/23/2010, excluding Weekends and Holidays.
    Expand|Select|Wrap|Line Numbers
    1. MsgBox fCalcWorkDays(#12/23/2010#, 7)
  5. The return Value will be:
    Expand|Select|Wrap|Line Numbers
    1. 1/4/2011
  6. EXPLANATION:
    Expand|Select|Wrap|Line Numbers
    1. Start Date: 12/23/2010
    2. Day 01: 12/24/2010 - No Good, a Holiday listed in tblHolidays
    3. Day 02: 12/25/2010 - No Good, a Holiday listed in tblHolidays
    4. Day 03: 12/26/2010 - No Good, a Sunday
    5. Day 04: 12/27/2010 - Good, Monday - a valid Workday (1st)
    6. Day 05: 12/28/2010 - Good, Tuesday - a valid Workday (2nd)
    7. Day 06: 12/29/2010 - Good, Wednesday - a valid Workday (3rd)
    8. Day 07: 12/30/2010 - Good, Thursday - a valid Workday (4th)
    9. Day 08: 12/31/2010 - Good, Friday - a valid Workday (5th)
    10. Day 09: 1/1/2011 - No Good, a Holiday & also a Weekend (Saturday)
    11. Day 10: 1/2/2011 - No Good, a Sunday
    12. Day 11: 1/3/2011 - Good, Monday - a valid Workday (6th)
    13. Day 12: 1/4/2011 - Good, Tuesday - a valid Workday (7th) ==> BRAVO!!
    14.  
  7. I know that it is a lot to absorb, but if you have any problems, simply ask, and one of us will be happy to assist you.
Nov 5 '10 #4

P: 10
Adezzi, I appreciate your help and sorry about not getting back to you sooner...swamped. I understand the explanations/examples, and creating the tblHoliday and copying the code to a standard Module. However, I got lost at # 4. Call the Function in some manner, passing to it a Start Date...

Is the name of the function fCalcWorkDays? How do I call it and pass to it a start date?
Nov 10 '10 #5

ADezii
Expert 5K+
P: 8,675
Are you performing the Workday Calculations for a Single Date/Workday Interval or are these Start Dates stored in a Table, and you wish to perform the Calculations for all Start Dates in the Table?
Nov 10 '10 #6

P: 10
Actually there are several start dates (i.e. "date sent drug"; "date sent physical", "date sent HIT"). The "date sent drug" has 2 days excluding weekends and holidays to respond; the "date sent physical and "date sent HIT" has 4 days excluding weekends and holidays to respond. These required dates are not currently in a separate table.
Nov 10 '10 #7

ADezii
Expert 5K+
P: 8,675
After careful consideration, I am going to create a Demo consisting of 3 individual Queries that will perform the Calculations. For the purpose of this Demo, all Date Fields will be in the same Table. This should show you exactly how to perform the Calculations. Stay tuned to this station...
Nov 10 '10 #8

P: 10
Adezii...You are definitely my hero! Thank you so much.
Nov 10 '10 #9

ADezii
Expert 5K+
P: 8,675
First of all, you are quite welcome. This should do it for you, Sabrina. Download the Attachment, and have a good look at it, hopefully it accomplishes what you have requested. If you are stuck on anything, let us know, we'll be here. In any event, kindly let us know one way or the other.
Attached Files
File Type: zip Workdays.zip (21.9 KB, 113 views)
Nov 10 '10 #10

P: 10
Oh my goodness IT WORKS! Whoo Hoo I am so happy! I can't tell you how much I appreciate your help on this...
Nov 12 '10 #11

ADezii
Expert 5K+
P: 8,675
The pleasure was all mine, Sabrina.
Nov 12 '10 #12

P: 10
ADezii,

I have one more question concerning this...Is there a way to determine how many days (minus holidays and weekends) there are between the date the test results are received and the date the tests were due using the fCalcWorkDays function?
Nov 12 '10 #13

ADezii
Expert 5K+
P: 8,675
  1. What you wre requesting can be accomplished, but first I need some clarification.
  2. Are you now speaking about different Dates, namely [Date Results Due] and [Date Results Received]?
  3. Does the Logic have to account for [Date Results Received] being possibly equal to (=) [Date Results Due], greater than (>) [Date Results Due], or less than (<) [Date Results Due]?
  4. Kindly post some Data illustrating exactly what you are looking for, as well as the results. I also need to know what Holidays are posted in tblHolidays.
Nov 12 '10 #14

P: 10
Please let me know if this doesn't make sense.

2. Yes. The results would be the # of days different between the Date Results Received minus and the Date Results Due (excluding Holidays and Weekends).

3. Yes. The difference would reflect the # of days from "Date Results Received" from "Date Results Due".

4. Example:

Date Drug Sent 11/24/10
Date Drug Results Due: 11/30/10 (2 days minus weekends and 2 Holidays)
Date Drug Results Received: 12/2/10
Difference between Date Drug Results Received and Date Drug Results Due: 2

This is a list of what is in my tblHolidays Table:

Holiday
11/12/2010
11/25/2010
11/26/2010
12/24/2010
12/27/2010
12/31/2010
1/17/2011
2/21/2011
5/13/2011
5/30/2011
7/4/2011
9/5/2011
11/11/2011
11/24/2011
11/25/2011
12/23/2011
12/26/2011
1/2/2012
Nov 12 '10 #15

NeoPa
Expert Mod 15k+
P: 31,662
Sabrina, Attachments don't show up in the Best Answer. The point of the Best Answer post is to draw immediate attention to the one post, above all others, that best explains the answer.

I suggest you wait for further posts and I'm sure one of ADezii's (one of our best experts here) will turn up that is fully appropriate for Best Answer.

I leave you in his very capable hands :-)
Nov 12 '10 #16

ADezii
Expert 5K+
P: 8,675
What you are requesting involves similar concepts and logic. The general idea is to analyze each Day between [Date Results Received] and [Date Results Due] inclusive of the later Date. If a Day is not a Weekend and not a Holiday, it is added to an Aggregate Total. When all Days have been processed, this Total is then returned by the Function. To the Function you pass the Received and Due Dates, and it will do the rest. I basically through this Code together since I am rather pressed for time, so it is probably not optimized, but it appears functional. I attached a second Demo for your review. Let me know if this works for you, Sabrina.
P.S. - I'll post the Function for reference even though I included an Attachment. Again, this all assumes that the Date Fields are Required, and cannot be NULL.
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcWorkdaysDiff(dteReceived As Date, dteDue As Date) As Integer
  2. Dim intDayCtr As Integer
  3. Dim dteTheDate As Date
  4. Dim intWkDay As Integer
  5.  
  6. If dteReceived = dteDue Then
  7.   fCalcWorkdaysDiff = 0
  8. ElseIf dteReceived > dteDue Then
  9.   For intDayCtr = 1 To DateDiff("d", dteDue, dteReceived)
  10.     dteTheDate = DateAdd("d", intDayCtr, dteDue)
  11.       'If dteTheDate is a Weekday AND NOT a Holiday as listed in the [Holiday] Field of tblHolidays
  12.       If Not (Weekday(dteTheDate) Mod 7 < 2) And _
  13.               DCount("*", "tblHolidays", "[Holiday] = #" & dteTheDate & "#") = 0 Then
  14.                 intWkDay = intWkDay + 1
  15.       End If
  16.   Next
  17.     fCalcWorkdaysDiff = intWkDay
  18. Else        'dteReceived < dteDue
  19.   For intDayCtr = 1 To DateDiff("d", dteReceived, dteDue)
  20.     dteTheDate = DateAdd("d", intDayCtr, dteReceived)
  21.       'If dteTheDate is a Weekday AND NOT a Holiday as listed in the [Holiday] Field of tblHolidays
  22.       If Not (Weekday(dteTheDate) Mod 7 < 2) And _
  23.               DCount("*", "tblHolidays", "[Holiday] = #" & dteTheDate & "#") = 0 Then
  24.                 intWkDay = intWkDay + 1
  25.       End If
  26.   Next
  27.     fCalcWorkdaysDiff = intWkDay
  28. End If
  29. End Function
Attached Files
File Type: zip Workday Diff.zip (17.3 KB, 92 views)
Nov 12 '10 #17

P: 10
ADezzi, It works like a charm! You are awesome! Again, I can't thank you enough!
Nov 15 '10 #18

NeoPa
Expert Mod 15k+
P: 31,662
Post #17 might make a good Best Answer Sabrina ;-)
Nov 15 '10 #19

P: 10
I agree...since I'm new to this process...do I just click on the "choose as best answer" button?
Nov 15 '10 #20

ADezii
Expert 5K+
P: 8,675
Glad it worked for you, Sabrina. As I previously indicated in Post #17, I basically threw this code together, and it is not really optimized. It may also contain a Logic Bug, that only recently became apparent. Since the Date Received can either be equal to, greater than, or less than the actual Due Date, I coerce the DateDiff() Function into always returning a positive result by flip-flopping the Due and Received Dates. Since the 2nd Date in always inclusive in the calculations, it can skew the results by 1 should it fall on a Holiday or Weekend. In simpler terms if the Due Date is Nov. 15, 2010 and the Received Date Nov. 19, 2010, the Number of Workdays may not be the same as if the Received Date is Nov. 11, 2010. The number of Days from the Due Date are exactly the same, but since Nov. 11 or Nov. 19 may/may not qualify as a Workday, you could be off by 1. Enough gibberish, If you notice that you appear to be off in your calculations by 1, let me know and we'll see what we can due.
Nov 15 '10 #21

NeoPa
Expert Mod 15k+
P: 31,662
Sabrina:
do I just click on the "choose as best answer" button?
Basically yes. First you must find the post that you want to select of course. From there, just click on the button linked to the post.

You've managed to do it before if you recall (I reset it as the post chosen was not appropriate for that).
Nov 15 '10 #22

Post your reply

Sign in to post your reply or Sign up for a free account.