End Date Function (1 Viewer)

Philkred · Aug 22, 2019

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 · Aug 22, 2019

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

jdraw · Aug 22, 2019

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 · Aug 22, 2019

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)

Code:

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;

Code:

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!

End Date Function (1 Viewer)

Philkred

New member

theDBguy

I’m here to help

jdraw

Super Moderator

Philkred

New member

Similar threads

Users who are viewing this thread