End Date Function

[Philkred]

Hi All, I'm looking for help/advice in regards to calculating an end date for a variable period of time in minutes factoring in, working days, fixed working hours, fixed break times and holidays.

I've tried to work it out via queries but kept hitting either a "Query is too complex" issue (mainly due to my lack of knowledge :banghead or the date and finish time were just incorrect. I'm thinking maybe the best solution is to create a function around this, and in the meantime (while I continue struggle to put that together :banghead I wondered if any of you experts out there in this forum know if this has already been done before or you know how to put something like that together, so I can learn and adapt it to my db.

I hope this all makes sense and I would really appreciate any help on this as its driving me mad!

 

[theDBguy]

Hi. Welcome to AWF! With that many requirements, it sounds like you might need a custom function to calculate the End Date.

 

[jdraw]

Hi All, I'm looking for help/advice in regards to calculating an end date for a variable period of time in minutes factoring in, working days, fixed working hours, fixed break times and holidays.

What have you done so far? What is/are your table/design(s)?
Can you provide a few examples showing your input data and your expected results?
Welcome aboard AWF.

 

[Philkred]

Hey, thanks for the welcome messages!

So I had two queries as per below, I've been editing them since so forgive the bad layouts/inconsistencies but hopefully you will get where I tried to go with it (I'm very much a newbie with Access/VBA/SQL)

SELECT 
tblWorkHours.Workday_Start_Time, 
tblWorkHours.Workday_End_Time, 
qryTemp_Scheduled_Assets_Sum.SumOfQty, 
tblConfig_Task_Types.Limit_Per_Hour, 
tblTemp_Scheduled_Projects.Project_Start_Date, 
tblTemp_Scheduled_Projects.Project_Start_Time, 
qryTemp_Scheduled_Engineers_Count.CountOfEngineer_ID, 
[Project_Start_Date]+[Project_Start_Time] AS [Date], 
DateDiff("n",[Project_Start_Time],[Workday_End_Time]) AS FirstDayMins, 
IIf(([SumOfQty]/[Limit_Per_Hour]/[CountOfEngineer_ID]*60)>[FirstDayMins],([SumOfQty]/[Limit_Per_Hour]/[CountOfEngineer_ID]*60)+DateDiff("n",[Workday_Start_Time],[Project_Start_Time]),([SumOfQty]/[Limit_Per_Hour]/[CountOfEngineer_ID]*60)) AS WorkMinsCalc, 
IIf([WorkMinsCalc]/7.5/60<1,0,[WorkMinsCalc]/7.5/60) AS WorkDays, 
IIf(DateDiff("n",[Project_Start_Time],[Workday_End_Time])>315 And DateDiff("n",[Project_Start_Time],[Workday_End_Time])<450,DateDiff("n",[Project_Start_Time],[Workday_End_Time])+60,IIf(DateDiff("n",[Project_Start_Time],[Workday_End_Time])>225 And DateDiff("n",[Project_Start_Time],[Workday_End_Time])<315,DateDiff("n",[Project_Start_Time],[Workday_End_Time])+45,IIf(DateDiff("n",[Project_Start_Time],[Workday_End_Time])>120 And DateDiff("n",[Project_Start_Time],[Workday_End_Time])<225,DateDiff("n",[Project_Start_Time],[Workday_End_Time])+15,DateDiff("n",[Project_Start_Time],[Workday_End_Time])))) AS FirstDayCalcMultipleDays, 
IIf([WorkDays]>1,DateAdd("n",((Int([WorkDays])-1)*990)+([FirstDayMins]+930)+[WorkMinsCalc],[Date]),DateAdd("n",[WorkMinsCalc],[Date])) AS DateCalcs, 
IIf(Format([DateCalcs],"Short Time")>[Workday_End_Time],DateAdd("n",-DateDiff("n",[Workday_End_Time],Format([DateCalcs],"Short Time"))+930+(([WorkDays]-Int([WorkDays]))*60*7.5),[DateCalcs]),[DateCalcs]) AS DateCalcs2, 
IIf([WorkDays]>1,dhNextWorkdayA([DateCalcs2]),[DateCalcs2]) AS EstEndDateCalc,
DateDiff("n",[Project_Start_Time],Format([EstEndDateCalc],"Short Time")) AS EstEndDayMins

FROM tblTemp_Scheduled_Projects, qryTemp_Scheduled_Engineers_Count, qryTemp_Scheduled_Assets_Sum, tblWorkHours, tblConfig_Task_Types INNER JOIN tblTemp_Scheduled_Tasks ON tblConfig_Task_Types.Task_ID = tblTemp_Scheduled_Tasks.Task_ID;

SELECT 
qryEst_End_Date_Calc.WorkDays, 
IIf([EstEndDayMins]>330 And [EstEndDayMins]<450,[EstEndDayMins]+60,IIf([EstEndDayMins]>225 And [EstEndDayMins]<330,[EstEndDayMins]+45,IIf([EstEndDayMins]>135 And [EstEndDayMins]<225,[EstEndDayMins]+15,[EstEndDayMins]))) AS EstEndMinsWBrk,
qryEst_End_Date_Calc.EstEndDateCalc AS EstEndDC, 
Format([EstEndDC],"Short Date") AS EndDate, Format([EstEndDC],"Short Time") AS EndTime

FROM qryEst_End_Date_Calc;

My idea was that I just needed calculate the breaks on the first day and last day add those in and check for triggering the end time of 16:30. Also, I used the dhNextWorkdayA but this adds an additional day where its not needed, but that might be me messing it up again, not sure, mainly just making this up as I go and I might be massively over thinking or under thinking this whole thing lol!