Function doesn't run, i'm despair

[nilses]

Hello,

I NEED YOUR HELP. I have advanced in my problem but I've a problem on this function. I search the solution since two days and i didn't find it. This function make the difference between two dates but i use a timetable 09H00-12H00//13H30-17H30. After this i subtract the bank holiday, sunday and saturday afternoon. In this exemple below, the function run perfectly for one day or most....(the time is on second)

----StartDate--------------EndDate----------------Ifound--------IfoundByMyFunction
14/05/03 18:00:00-----15/05/03 10:00:00-------3600-------------3600
14/05/03 18:00:00-----15/05/03 17:00:00-------23400------------23400
15/05/03 07:00:00-----15/05/03 20:00:00-------25200------------25200
15/05/03 10:00:00-----15/05/03 18:00:00-------21600------------21600

13/06/03 10:00:00-----23/06/03 15:00:00------185400-----------185400
13/06/03 10:00:00-----23/06/03 10:00:00------172800-----------172800
13/06/03 10:00:00-----21/06/03 10:00:00------162000-----------162000
13/06/03 10:00:00-----21/06/03 15:00:00------169200-----------169200
13/06/03 15:00:00-----23/06/03 10:00:00------160200-----------160200
13/06/03 15:00:00-----21/06/03 15:00:00------156600-----------156600
13/06/03 15:00:00-----21/06/03 10:00:00------149400-----------149400
13/06/03 15:00:00-----23/06/03 10:00:00------160200-----------160200
14/06/03 10:00:00-----23/06/03 14:00:00------156600-----------156600
14/06/03 10:00:00-----21/06/03 15:00:00------144000-----------144000
14/06/03 10:00:00-----21/06/03 11:00:00------140400-----------140400
14/06/03 16:00:00-----23/06/03 10:00:00------140400-----------140400


but when StartDate and Enddate is out of this timetable,9H00-12H00 // 13H30-17H30, the function doesn't run when EndDate is < 09H00 or when StartDate and EndDate is > 17H30 like this exemple below.

----StartDate--------------EndDate----------------Ifound----------IfoundByMyFunction
28/06/02 14:34:37-----29/06/02 08:31:20-------10523------------14677
-> Normally it's 17:30:00 - 14:34:37 = 10523

01/04/03 18:00:00-----02/04/03 18:00:00-------25200------------50400
-> Normally it's just one day because 01/04/03 > 17H30
18:00:00 - 09:00:00 = 7H00 or 25200 and not two days

24/06/03 14:34:37-----25/06/03 08:31:20-------10523------------14677
-> Normally it's 17:00:00 - 14:34:37 = 10523

This is the code and i remove the part for bank holiday, sunday and saturday afternoon: (This function is made by Harry )

Function TimeWorkedSeconds(StartDate As Date, EndDate As Date) As Double

Dim DayStart As Date
Dim LunchStart As Date
Dim LunchEnd As Date
Dim DayEnd As Date
Dim WholeDay As Integer
Dim StartTime As Date
Dim EndTime As Date
Dim WorkedTime As Date
Dim DaySecs As Double
Dim TimeSecs As Double
Dim DD As Long
Dim SD As Long
Dim HowManySunday As Double
Dim HowManySaturday As Double
Dim HowManyDayOff As Double
Dim HowManyDayOffSaturday As Double
Dim Result As Double

'DECLARE TIMES
DayStart = "09:00:00"
LunchStart = "12:00:00"
LunchEnd = "13:30:00"
DayEnd = "17:30:00"
DD = vbSunday
SD = vbSaturday

'WORK OUT WHOLE DAYS
WholeDay = Int(EndDate - StartDate)


'WORK OUT REMAINDER OF TIME
'START BY LIMITING TO BEGINNING AND END OF WORKING DAY AND ALTER WholeDay ACCORDINGLY
If Format(StartDate, "hh:mm:ss") < DayStart Then
StartTime = Format(DayStart, "hh:mm:ss")
ElseIf Format(StartDate, "hh:mm:ss") > DayEnd Then
StartTime = Format(DayStart, "hh:mm:ss")
WholeDay = WholeDay - 1
Else
StartTime = Format(StartDate, "hh:mm:ss")
End If

If Format(EndDate, "hh:mm:ss") < DayStart Then
EndTime = Format(DayEnd, "hh:mm:ss")
WholeDay = WholeDay - 1
ElseIf Format(EndDate, "hh:mm:ss") > DayEnd Then
EndTime = Format(DayEnd, "hh:mm:ss")
Else
EndTime = Format(EndDate, "hh:mm:ss")
End If

'FOR START TIME BEFORE END TIME
If StartTime < EndTime Then
If StartTime < LunchStart Then
If EndTime < LunchStart Then
WorkedTime = EndTime - StartTime
ElseIf EndTime < LunchEnd Then
WorkedTime = LunchStart - StartTime
Else
WorkedTime = EndTime - StartTime + LunchStart - LunchEnd
End If

ElseIf StartTime < LunchEnd Then

If EndTime < LunchEnd Then
WorkedTime = 0
Else
WorkedTime = EndTime - LunchEnd
End If
Else
WorkedTime = EndTime - StartTime
End If
ElseIf StartTime = EndTime Then
WorkedTime = 0
Else
If StartTime < LunchStart Then
WorkedTime = DayEnd - StartTime + LunchStart - LunchEnd + EndTime - DayStart
ElseIf StartTime < LunchEnd Then
If EndTime < LunchStart Then
WorkedTime = DayEnd - LunchEnd + EndTime - DayStart
Else
WorkedTime = DayEnd - LunchEnd + LunchStart - DayStart
End If
Else
If EndTime < LunchStart Then
WorkedTime = DayEnd - StartTime + EndTime - DayStart
ElseIf EndTime < LunchEnd Then
WorkedTime = DayEnd - StartTime + LunchStart - DayStart
Else
WorkedTime = DayEnd - StartTime + EndTime - LunchEnd + LunchStart - DayStart
End If
End If
End If


'TIME WORKED!!
DaySecs = (DayEnd - LunchEnd + LunchStart - DayStart) * 86400 * WholeDay
TimeSecs = WorkedTime * 86400
Result = DaySecs + TimeSecs

TimeWorkedSeconds = Result

'IF THE RESULT < 0 THEN RESULT = 0 !!
'If TimeWorkedSeconds < 0 Then
' TimeWorkedSeconds = 0
'End If

End Function


Your help will be appreciate because i'm blocked and i didn't find the solution

Nillses

 

[Travis]

Your problem is with the Wholeday calculation

You have

'WORK OUT WHOLE DAYS
WholeDay = Int(EndDate - StartDate)

use this:

WholeDay = DateDiff("d", StartDate, EndDate)


The WholeDay was being set to -1


I tested using the other times you had and I came up with the answers that you previously had for the part that worked and the answers you expected for the part that did not work.