Add a specified number of days to a date, passing weekends and holidays

[newbiecoder]

Hello everyone! I have to calculate a due date (containing time) by taking todays date and adding the number of days remaining. However, I need to skip past weekends and holidays and only count business days while doing this. I have a table named "tblDaysRemainingCalculation" that contains the number of days remaining until a ticket is due. I also have a table named "tblBusinessDayTracker" that only contains a list of business days (excluding weekends and holidays) for the whole year. Can someone please help me find a solution to my problem?



tblBusinessDayTracker

WORK_DATE

5/8/2018

5/9/2018

5/10/2018

5/11/2018 (Friday)

5/14/2018 (Monday)

tblDaysRemainingCalculation

TICKET_NO	DAYS_REMAINING	SLA_DUE_DATE (Adds Days_Remaining to Todays date)
111945092	-53.80241	3/16/2018 1:57:00 PM (Dates can be in the past)
112203180	3.13580	5/12/2018 12:28:01 PM (Should be 5/14/2018 12:28:01 PM)

[Minty]

You shouldn't be storing the ETA and certainly not in a separate table, that ETA can always be calculated assuming you have a Agreed turnaround stored per customer or a general one.

From there you simply need a working days calculations - there are dozens of threads on this subject on here, simply look at the bottom of the thread below for links.

[newbiecoder]

Unfortunately, I have not had success in finding a solution that fits my needs which is why I am here. I need to store the ETA to reference for other purposes. All I want to do is add my days_remaining column to todays date, but instead of going off of calendar days I want to go off of my already created list of business days that I already use as a reference for other things.

[isladogs]

This is a commonly asked question and a forum search will give lots of hits.
Start by looking at the five similar threads at the bottom of this page.

[Minty]

What is your days remaining fields data (I can see it's a number), how are you getting to that value?

The solution using your existing data will be to write a simple function that counts the number of records in your business days table Between a StartDate and an Enddate something like

Function BusinessDays(StartDate as Date, EndDate as Date)

BusinessDays = DCount("*","tblBusinessDayTracker","[Work_Date] Between #" Format(StartDate ,"yyyy/mm/dd") & "# And #" & Format(EndDate,,"yyyy/mm/dd") & "#")

End Function

You might want to check for valid dates and put in the current date as a default, but hopefully you'll get the idea.

[newbiecoder]

Days_Remaining is calculated by: stage complete date - stage start date (excluding weekends/holidays). I do that for every instance that a ticket is in my teams que, and then I add them together grouping them by the ticket number. Then, I subtract that number from our service level agreement to determine how long we have left to complete the ticket.

[newbiecoder]

Is there a way that I can find a record in my business days table (todays date), and offset down my list by the number of Days_remaining? Then pull that record (the due date) into tblDaysRemainingCalculation. That would even account for when the due date is in the past I would think.

[orange]

See if this function in the code repository offers any insight.

[Gicu]

I think you would need to use a dlookup for that, something like this:

dlookup("[WORK_DATE]","[tblBusnessDaysTracker]","[WORK_DATE]=#" & Date +dlookup("[DATE_REMAINING]","[tblDaysRemainingCalculation]","[TICKET_NO] =" & [TICHET_NO]) & "#")

The formula assumes TICHET_NO is a number.

Cheers,
Vlad

[Gicu]

Sorry, I forgot about the time part, you would need to add after you get your date from the tblBusinessDaysTracker table with the dlookup.
Vlad

[newbiecoder]

Ticket_NO is short text, and am I placing the dlookup in the "default value field"?

[newbiecoder]

Ticket_NO is short text, and am I placing the dlookup in the "default value field"? When I added this to the default value field, it came back with an error "the database engine does not recognize either the field TICKET_NO in a validation expression, or the default value in the table 'tblDaysRemainingCalculation"

[Gicu]

default value of a form or the table? I would use it in a form or maybe in a update query if you really need to store it. Because tichet_no is text you need to modify the dlookup:
dlookup("[WORK_DATE]","[tblBusnessDaysTracker]","[WORK_DATE]=#" & Date +dlookup("[DATE_REMAINING]","[tblDaysRemainingCalculation]","[TICKET_NO] ='" & [TICHET_NO] &"'") & "#")

Cheers,
Vlad

[newbiecoder]

Default value in a table (tblDaysRemainingCalculation) in a column named (SLA_Due_Date) Also, I tried adding the new dlookup and I am still receiving the same error

[Gicu]

you cannot use the default value of the table, it needs to be in a form, report or query.