473,324 Members | 2,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to convert Excel Formula for Access 2003

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

21 4153
ADezii
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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
Adezii...You are definitely my hero! Thank you so much.
Nov 10 '10 #9
ADezii
8,834 Expert 8TB
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, 144 views)
Nov 10 '10 #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
8,834 Expert 8TB
The pleasure was all mine, Sabrina.
Nov 12 '10 #12
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
8,834 Expert 8TB
  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
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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, 108 views)
Nov 12 '10 #17
ADezzi, It works like a charm! You are awesome! Again, I can't thank you enough!
Nov 15 '10 #18
NeoPa
32,556 Expert Mod 16PB
Post #17 might make a good Best Answer Sabrina ;-)
Nov 15 '10 #19
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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

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

Similar topics

0
by: Bob Alston | last post by:
Looking for the best resources for converting an existing Access 2003 database to the web. Have an application that has many forms and reports, with sub-forms and sub-reports and liberal use of...
0
by: David G. | last post by:
The keyboard type ahead buffer does not seem to work in Access 2003 in certain situations. We would like some help with this. Here are the details. We have a large program that was developed...
3
by: Decreenisi | last post by:
Hi, I have to convert an existing spreadsheet for rejects into an access database. My problem is I use a load of lookup tabels in excel. How do I approch this in access. Also, just a general...
6
by: james.igoe | last post by:
I've been hired to produce a reporting database that takes data from numerous sources (5 financial products, from three regions, each with multiple tabs) and although I'm confident I can build...
1
by: John Overton | last post by:
I have a new requirement to Import and Export Excel tables into and out of Access 2003. The Excel tables are located on a remote server but I know the path to the Excel Files. Can you please help...
0
by: sandervanee | last post by:
Hello, I am trying to export several Access 2003 select queries to Excel 2003 using an Access macro. I'm using the macro command "TransferSpreadsheet" to export the queries. This going quit well,...
18
by: Dirk Hagemann | last post by:
Hello, From a zone-file of a Microsoft Active Directory integrated DNS server I get the date/time of the dynamic update entries in a format, which is as far as I know the hours since january 1st...
1
by: debio4ko | last post by:
Hi, all! Good to join your forum, this is my first post! :) Actually I've tried first the search engine to find a solution for my question, but there was no success. So: Basically I have several...
1
by: jmarcrum | last post by:
Hello, I have a question. I have a small database that keeps track of work that a contractor is doing for my company. Once a month I attach the database in an email and send it to the contractor...
3
by: david sherman | last post by:
Does software converting excel to access with financial functions exist? I need to take several fixed income portfolio run manually from excel and create a database. The excel relies heavily on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.