Results 1 to 6 of 6
  1. #1
    Teatimedgg is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    3

    How to add business days and subtract holidays to a date field


    Hi Everyone,


    I have a database that has a begin date field and also a SLA field (# of days), I want to create field that will give me the beginning date plus the SLA days to equal a date. Work days only and subtract holidays.

    Example: Begin date 3/20/20 plus SLA 3 days New Date: 3/25/20 If there was a holiday then it would be 3/26/20.

    Any help would be appreciated!!

    thanks!


    Tea

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,069
    No idea what an sla day is an no idea if that field contains any values or if you're updating it with this solution. However, any time that holidays are involved you need a table to identify all the holidays along with their dates and populate it with records. When you run out of records, things usually stop working properly. This is because holiday dates are not universal, nor are they all consistent.

    Maybe one or both of these functions will do the trick for you
    http://access.mvps.org/access/datetime/date0006.htm
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    Join Date
    Apr 2017
    Posts
    1,101
    The easiest way is to have a calendar table in your app, or in specific app on network resource available for all other apps. The structure of table for current case will be like tblCalendar: CalDate, DayType (where DayType has values 1-workday, 2-weekend, 3-holiday). There may be other fields too you'll need in other apps, like week number in format "YYYYYWW", weekday, month number in format "YYYYMM", number of working hours, etc. You fill this calendar table for some number of years in advance (you spend some time entering/calculating all fields, but with big enough years buffer it will be one-time work, or you have to do this only after some number of years again).

    In case you have this calendar table in separate resources app, you link it to your current app (otherwise it simply is there ). And now your task is easy - you have to locate the date which is the number (= SLA) of CalDates with DayType = 1 later than begin date. The best way to do this is to write an UDF for this.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,846

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,230
    No idea what an sla day
    suspect it means 'service level agreement'. So an sla of 3 days means the company will respond within 3 days of the incident/enquiry/whatever

  6. #6
    Teatimedgg is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    3
    Thank you everyone, i think i will take care of this in excel first and then import to access. :-)

    Tea

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-29-2019, 01:11 PM
  2. Replies: 36
    Last Post: 05-10-2018, 02:02 PM
  3. Replies: 2
    Last Post: 10-11-2017, 09:33 PM
  4. Calculate Business Hours over X amount of business days.
    By gutarkomp in forum Code Repository
    Replies: 5
    Last Post: 05-16-2017, 06:23 PM
  5. Add or Subtract Business Days from a Given Date
    By orange in forum Code Repository
    Replies: 2
    Last Post: 05-15-2017, 12:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums