Besides the WorkingHours table, you will need a holiday master to keep a record of holidays. It should just have a date field. You will also need an overtime table which will have Date, EmployeeID, FromTime,ToTime. In case somebody works on holidays, you will need a HolidayWorked table, with EmployeeID and Date.
Giving you what the function could look like.
Function GetTime
lparameters EmpID,StartDateTime,EndDateTime
if date(StartDateTime)=date(EndDateTime)
if holiday
if not workingonholiday
return 0
endif
StartDateTime=Max(StartDateTime,StartofDayTime) && From the WorkingOnHoliday Table
EndDateTime=Min(EndDateTime,EndofDayTime) && From the WorkingOnHoliday Table
return EndDateTime-StartDateTime
endif
StartDateTime=Max(StartDateTime,StartofDayTime) && Take care of overtime here
EndDateTime=Min(EndDateTime,EndofDayTime) && Take care of overtime here
return EndDateTime-StartDateTime
endif
lnTotalTime=0
For ldDate=ttod(StartDateTime) to ttod(EndDateTime)
if ldDate=ttod(StartDateTime)
lnCurrentDateTime=GetTime(EmpId,StartDateTime,ttod(ldDate)+23:59:59)
lnTotalTime=lnTotalTime+lnCurrentDateTime
loop
endif
if ldDate=ttod(EndDateTime)
lnCurrentDateTime=GetTime(EmpId,ttod(EndDateTime)+'0:0:0',EndDateTime)
lnTotalTime=lnTotalTime+lnCurrentDateTime
loop
endif
lnTotalTime=lnTotalTime+GetTime(EmpId,ldDate+'0:0:0',ldDate+'23:59:59')
endfor
return lnTotalTime
Regards
Tushar
> Hi Guys,
>
> I have a challenge (love that) that I am struggling with (don't like that). I was wondering if anyone could help me with some ideas to solve the problem.
>
> I need to be able to calculate durations based on a working day.
>
> For example: (Presume start of day is 09:00 and end of day is 17:00)
>
> Start Time: 2008/09/15 16:00
> End Time: 2008/09/16 10:00
>
> Subtract the two to get duration I need 2 hours.
>
> But then I also need to take into account overtime, if soemone worked late on a job:
>
> Start Time: 2008/09/15 16:00
> End Time: 2008/09/16 18:00 (1 overtime hour)
>
> Duration would equal: 10 hours (1 from the 1st day and 9 from the 2nd).
>
> Cheers,
> -mark
>
>
>
> =====================
>
www.mapbusinesssolutions.com>
www.restaurantlogbook.com> =====================