449,386 Members | 1,858 Online
Need help? Post your question and get tips & solutions from a community of 449,386 IT Pros & Developers. It's quick & easy.

# Calculate a job End Date

 P: n/a Hi Access Building Friends, I am building a database for a manufacturer who needs to know the projected End_Date of each job. I know the Start_Date and the total days required to do the job. I need to calculate the End_Date after taking into account Weekends and Holidays. I have a table which lists the dates of each holiday for several years to come. I tried using a code I found called, "Public Function WorkingDays" which calculates the number of working days and holidays between two dates, but it doesn't push the End_Date ahead to fulfill the working days needed. I appreciate any help you can offer. Thanks, Connie -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200809/1 Sep 9 '08 #1
11 Replies

 P: n/a okay, try this function, as Public Function calcDate(ByVal dat As Date, ByVal intAdd As Integer) As Date Dim i As Integer, bump As Boolean, str As String dat = CDate(Fix(dat)) i = 1 For i = 1 To intAdd dat = dat + 1 Do bump = False If DCount(1, "tbl00Holidays", "hDate = #" & dat & "#") 0 Then dat = dat + 1 bump = True End If str = Format(dat, "ddd") If str = "Sat" Then dat = dat + 2 bump = True ElseIf str = "sun" Then dat = dat + 1 bump = True End If Loop Until bump = False Next calcDate = dat End Function the arguments are: a beginning date, and a number of days. the function returns the calculated end date. the function uses a table that holds holidays and any other dates (outside of weekends) that should not be counted. the table is tbl00Holidays, and the date field is hDate - but of course you can substitute the appropriate table and field names from your holidays table. hth "Connie via AccessMonster.com"

 P: n/a "Connie via AccessMonster.com" = 2 And _ Weekday(BegDate + t) <= 6 And _ IsNull(Dlookup("Holiday","tblHolidays","Holiday=#" & (BegDate + t) & "#")) Then w = w + 1 Endif If w=WorkDays Then Exit Loop Endif Loop FindEndDate = BegDate + t End Function Sep 9 '08 #3

 P: n/a Hi Tina, Thank you for your speedy reply. I am excited to try your code. Connie :) tina wrote: >okay, try this function, asPublic Function calcDate(ByVal dat As Date, ByVal intAdd As Integer) As Date Dim i As Integer, bump As Boolean, str As String dat = CDate(Fix(dat)) i = 1 For i = 1 To intAdd dat = dat + 1 Do bump = False If DCount(1, "tbl00Holidays", "hDate = #" & dat & "#") 0 Then dat = dat + 1 bump = True End If str = Format(dat, "ddd") If str = "Sat" Then dat = dat + 2 bump = True ElseIf str = "sun" Then dat = dat + 1 bump = True End If Loop Until bump = False Next calcDate = datEnd Functionthe arguments are: a beginning date, and a number of days. the functionreturns the calculated end date. the function uses a table that holdsholidays and any other dates (outside of weekends) that should not becounted. the table is tbl00Holidays, and the date field is hDate - but ofcourse you can substitute the appropriate table and field names from yourholidays table.hth >Hi Access Building Friends, [quoted text clipped - 17 lines] >Thanks,Connie -- Message posted via http://www.accessmonster.com Sep 9 '08 #4

 P: n/a Hi Fred, Thank you for reading my post and giving my your "air" code. I am hopeful. Connie :) Fred Zuckerman wrote: >Hi Access Building Friends, [quoted text clipped - 19 lines] >Thanks,Connie You might try something like the function below. It's untested "air" codebut it might work ;) I've made the assumption that you have a table namedtblHolidays with a date field named Holiday that contains all of yourholidays. The function takes two parameters (BegDate and WorkDays) and willreturn the EndDate.Fred ZuckermanPublic Function FindEndDate (BegDate As Date, WorkDays As Integer) As Date Dim t As Integer 'Total Days Dim w As Integer 'Work Days t = 0 w = 0 Do While True t = t + 1 ' Check To See If Date Is Sat, Sun, or Holiday If Weekday(BegDate + t) >= 2 And _ Weekday(BegDate + t) <= 6 And _ IsNull(Dlookup("Holiday","tblHolidays","Holiday=#" & (BegDate + t)& "#")) Then w = w + 1 Endif If w=WorkDays Then Exit Loop Endif Loop FindEndDate = BegDate + tEnd Function -- Message posted via http://www.accessmonster.com Sep 9 '08 #5

 P: n/a Hi Tina, This code works great! Thank you so much. :) Could you tell me how to make it include the Start_Time and an End_Time? I'm thinking it has to do with the "As Date" setting, but don't know how to make it, "As Date/Time" Thanks again, Connie tina wrote: >okay, try this function, asPublic Function calcDate(ByVal dat As Date, ByVal intAdd As Integer) As Date Dim i As Integer, bump As Boolean, str As String dat = CDate(Fix(dat)) i = 1 For i = 1 To intAdd dat = dat + 1 Do bump = False If DCount(1, "tbl00Holidays", "hDate = #" & dat & "#") 0 Then dat = dat + 1 bump = True End If str = Format(dat, "ddd") If str = "Sat" Then dat = dat + 2 bump = True ElseIf str = "sun" Then dat = dat + 1 bump = True End If Loop Until bump = False Next calcDate = datEnd Functionthe arguments are: a beginning date, and a number of days. the functionreturns the calculated end date. the function uses a table that holdsholidays and any other dates (outside of weekends) that should not becounted. the table is tbl00Holidays, and the date field is hDate - but ofcourse you can substitute the appropriate table and field names from yourholidays table.hth >Hi Access Building Friends, [quoted text clipped - 17 lines] >Thanks,Connie -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200809/1 Sep 9 '08 #6

 P: n/a comments inline. "Connie via AccessMonster.com" Thanks again, Connie tina wrote: okay, try this function, as Public Function calcDate(ByVal dat As Date, ByVal intAdd As Integer) As Date Dim i As Integer, bump As Boolean, str As String dat = CDate(Fix(dat)) i = 1 For i = 1 To intAdd dat = dat + 1 Do bump = False If DCount(1, "tbl00Holidays", "hDate = #" & dat & "#") 0 Then dat = dat + 1 bump = True End If str = Format(dat, "ddd") If str = "Sat" Then dat = dat + 2 bump = True ElseIf str = "sun" Then dat = dat + 1 bump = True End If Loop Until bump = False Next calcDate = dat End Function the arguments are: a beginning date, and a number of days. the function returns the calculated end date. the function uses a table that holds holidays and any other dates (outside of weekends) that should not be counted. the table is tbl00Holidays, and the date field is hDate - but of course you can substitute the appropriate table and field names from your holidays table. hth Hi Access Building Friends, [quoted text clipped - 17 lines] Thanks, Connie -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200809/1 Sep 10 '08 #7

 P: n/a Hi Tina, Actually my Start_Date is a concatenation of the Start_Date and Start_Time. That way it flows into a Start_Shift, and the time is in military time. So I need an End_Time that would flow into an End_Shift. It is for a plastics manufacturer. Thanks so much! :) Connie tina wrote: >comments inline. >Hi Tina,This code works great! Thank you so much. :) you're welcome, glad it works for you. :) >Could you tell me how to make it include the Start_Time and an End_Time?I'm thinking it has to do with the "As Date" setting, but don't know how tomake it, "As Date/Time" well, in Access, a date value stored as Date/Time data type *always* has atime - a default of midnight, if nothing else. so the return value of thefunction does include a time, but it is the default. in fact, i specificallyincluded code in this function to strip the specific time out of the datevalue before beginning the "bump" cycle:dat = CDate(Fix(dat))since you're beginning with a "Start" date and bumping that date by x numberof working days to get an "End" date, might i ask why you need to work withtime values? >Thanks again, [quoted text clipped - 47 lines] >Thanks,Connie -- Message posted via http://www.accessmonster.com Sep 10 '08 #8

 P: n/a sorry, hon, you've completely lost me. i can't picture how you would *concatenate* two date/time values - are your Start_Date and Start_Time stored as Date/Time data type, or as Text data type? also, i don't understand what you mean by "flows into"...? "Connie via AccessMonster.com"

 P: n/a Hi Tina, Oops! I guess I meant "add together" the date and time... So let's say the Job Scheduler enters a Start Date of 9/12/08 and a Start Time of 22:30 (the beginning of 3rd shift), I made a field called [Start_Date_Time] which just adds the Start_Time to the Start_Date so I can use it in the calculation of the End_Date_Time. Then I separate that into End_Date and End_Time so I can use them in reports. The calcDate retains the original time part of the Start_Time, and adds days appropriately, but not the fraction of the last day. I have been trying all day to get the code to show the time part. It seems to be rounding down the intAdd to the nearest whole number. By the way, I am working on a button to "Check Capacities" which will prevent double booking of parts being scheduled to be built on machines.... which is the main reason that I need the time part to show up is so they can know what shift it will end on. I really really appreciate your help. Thank you. Connie :) tina wrote: >sorry, hon, you've completely lost me. i can't picture how you would*concatenate* two date/time values - are your Start_Date and Start_Timestored as Date/Time data type, or as Text data type? also, i don'tunderstand what you mean by "flows into"...? >Hi Tina, [quoted text clipped - 36 lines] >Thanks,Connie -- Message posted via http://www.accessmonster.com Sep 12 '08 #10

 P: n/a well, if you're adding whole days to the start date/time, as in 3 days is exactly 72 hours, then just comment out the line of code that strips the time out of the date value in the procedure, as ' dat = CDate(Fix(dat)) but if you need to add whole days and/or parts of days, that's another ball of wax. you could run the current procedure, adjusted as above, to add/bump days. but you'd need more code to handle adding hours and/or minutes, because adding more time could bump the date to the next day, which could be a weekend or holiday... anyway, you have a basic bump procedure already; with some thought you can probably modify the code to handle incrementing time. don't be afraid to play around with it, and remember the DateAdd() function - it may prove useful in working with time components. hth "Connie via AccessMonster.com"

 P: n/a Hi Tina. Thanks for your input. I'll work on that. tina wrote: >well, if you're adding whole days to the start date/time, as in 3 days isexactly 72 hours, then just comment out the line of code that strips thetime out of the date value in the procedure, as' dat = CDate(Fix(dat))but if you need to add whole days and/or parts of days, that's another ballof wax. you could run the current procedure, adjusted as above, to add/bumpdays. but you'd need more code to handle adding hours and/or minutes,because adding more time could bump the date to the next day, which could bea weekend or holiday... anyway, you have a basic bump procedure already;with some thought you can probably modify the code to handle incrementingtime. don't be afraid to play around with it, and remember the DateAdd()function - it may prove useful in working with time components.hth >Hi Tina, [quoted text clipped - 32 lines] >Thanks,Connie -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200809/1 Sep 13 '08 #12

### This discussion thread is closed

Replies have been disabled for this discussion.