| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF To Calculate Work hours between two dates Problem with code
Here is my code.
Function WkgHrs(StartTime As Date, EndTime As Date) As Single 'This function calculates the number of working hours between 'two date-time values. Working hours are defined as Mon - Thurs, '0430 - 2399.99 and Friday 0430-1030 hours. Fractions of hours are 'included in the calculations. Dim Hstart As Variant 'Starting hour array Dim Hend As Variant 'Ending hour array Dim DOW As Integer 'Day of week (1=Sunday, 2=Monday, 3=Tuesday, etc.) Dim DOWstart As Integer Dim DOWend As Integer Dim D As Date Dim DeltaH As Single 'Hours to be subtracted Dim Tend As Single Dim Tstart As Single Hstart = Array(0, 0, 4.5, 4.5, 4.5, 4.5, 4.5, 0) Hend = Array(0, 0, 23.99999, 23.99999, 23.99999, 23.99999, 10.5, 0) If EndTime - StartTime < 1 And Int(StartTime) < Int(EndTime) Then WkgHrs = 19.5 Else WkgHrs = 0 End If 'First sum hour for whole days For D = StartTime To EndTime DOW = Weekday(D) WkgHrs = WkgHrs + Hend(DOW) - Hstart(DOW) Next D 'Now subtract time for partial days DOW = Weekday(StartTime) '4:30 AM Tstart = 24 * (StartTime - Int(StartTime)) If Tstart Hstart(DOW) And Hstart(DOW) < 0 Then WkgHrs = WkgHrs - (Tstart - Hstart(DOW)) End If DOW = Weekday(EndTime) Tend = 24 * (EndTime - Int(EndTime)) If Tend < Hend(DOW) And Hend(DOW) < 24 Then WkgHrs = WkgHrs - (Hend(DOW) - Tend) End If End Function Here is my problem. I have two dates; 8/9/2007 17:37 and 8/10/2007 9:35. I thought, according to my code that this woudl produce a result of 11.46 hours. The result that it is producing is 24.97. Can anyone help me with this? Thanks, Jay |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
UDF To Calculate Work hours between two dates Problem with code
< I thought, according to my code that this woudl produce a result of 11.46 hours
Why? -- Kind regards, Niek Otten Microsoft MVP - Excel "jlclyde" wrote in message ups.com... | Here is my code. | Function WkgHrs(StartTime As Date, EndTime As Date) As Single | 'This function calculates the number of working hours between | 'two date-time values. Working hours are defined as Mon - Thurs, | '0430 - 2399.99 and Friday 0430-1030 hours. Fractions of hours are | 'included in the calculations. | Dim Hstart As Variant 'Starting hour array | Dim Hend As Variant 'Ending hour array | Dim DOW As Integer 'Day of week (1=Sunday, 2=Monday, 3=Tuesday, etc.) | Dim DOWstart As Integer | Dim DOWend As Integer | Dim D As Date | Dim DeltaH As Single 'Hours to be subtracted | Dim Tend As Single | Dim Tstart As Single | | Hstart = Array(0, 0, 4.5, 4.5, 4.5, 4.5, 4.5, 0) | Hend = Array(0, 0, 23.99999, 23.99999, 23.99999, 23.99999, 10.5, 0) | | If EndTime - StartTime < 1 And Int(StartTime) < Int(EndTime) Then | WkgHrs = 19.5 | Else | WkgHrs = 0 | End If | | 'First sum hour for whole days | For D = StartTime To EndTime | DOW = Weekday(D) | WkgHrs = WkgHrs + Hend(DOW) - Hstart(DOW) | Next D | | 'Now subtract time for partial days | DOW = Weekday(StartTime) '4:30 AM | Tstart = 24 * (StartTime - Int(StartTime)) | If Tstart Hstart(DOW) And Hstart(DOW) < 0 Then | WkgHrs = WkgHrs - (Tstart - Hstart(DOW)) | End If | | DOW = Weekday(EndTime) | Tend = 24 * (EndTime - Int(EndTime)) | If Tend < Hend(DOW) And Hend(DOW) < 24 Then | WkgHrs = WkgHrs - (Hend(DOW) - Tend) | End If | | End Function | | Here is my problem. I have two dates; 8/9/2007 17:37 and 8/10/2007 | 9:35. I thought, according to my code that this woudl produce a | result of 11.46 hours. The result that it is producing is 24.97. Can | anyone help me with this? | Thanks, | Jay | |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
UDF To Calculate Work hours between two dates Problem with cod
try:
Function WkgHrs(StartTime As Date, EndTime As Date) As Single 'This function calculates the number of working hours between 'two date-time values. Working hours are defined as Mon - Thurs, '0430 - 2399.99 and Friday 0430-1030 hours. Fractions of hours are 'included in the calculations. Dim Hstart As Variant 'Starting hour array Dim Hend As Variant 'Ending hour array Dim DOW As Integer 'Day of week (1=Sunday, 2=Monday, 3=Tuesday, etc.) Dim DOWstart As Integer Dim DOWend As Integer Dim D As Date Dim DeltaH As Single 'Hours to be subtracted Dim Tend As Single Dim Tstart As Single Hstart = Array(0, 0, 4.5, 4.5, 4.5, 4.5, 4.5, 0) Hend = Array(0, 0, 23.99999, 23.99999, 23.99999, 23.99999, 10.5, 0) 'First sum hour for whole days For D = Int(StartTime) To Int(EndTime) DOW = Weekday(D) WkgHrs = WkgHrs + Hend(DOW) - Hstart(DOW) Next D 'Now subtract time for partial days DOW = Weekday(StartTime) '4:30 AM Tstart = 24 * (StartTime - Int(StartTime)) If Tstart Hstart(DOW) And Hstart(DOW) < 0 Then WkgHrs = WkgHrs - (Tstart - Hstart(DOW)) End If DOW = Weekday(EndTime) Tend = 24 * (EndTime - Int(EndTime)) If Tend < Hend(DOW) And Hend(DOW) < 24 Then WkgHrs = WkgHrs - (Hend(DOW) - Tend) End If End Function "Niek Otten" wrote: < I thought, according to my code that this woudl produce a result of 11.46 hours Why? -- Kind regards, Niek Otten Microsoft MVP - Excel "jlclyde" wrote in message ups.com... | Here is my code. | Function WkgHrs(StartTime As Date, EndTime As Date) As Single | 'This function calculates the number of working hours between | 'two date-time values. Working hours are defined as Mon - Thurs, | '0430 - 2399.99 and Friday 0430-1030 hours. Fractions of hours are | 'included in the calculations. | Dim Hstart As Variant 'Starting hour array | Dim Hend As Variant 'Ending hour array | Dim DOW As Integer 'Day of week (1=Sunday, 2=Monday, 3=Tuesday, etc.) | Dim DOWstart As Integer | Dim DOWend As Integer | Dim D As Date | Dim DeltaH As Single 'Hours to be subtracted | Dim Tend As Single | Dim Tstart As Single | | Hstart = Array(0, 0, 4.5, 4.5, 4.5, 4.5, 4.5, 0) | Hend = Array(0, 0, 23.99999, 23.99999, 23.99999, 23.99999, 10.5, 0) | | If EndTime - StartTime < 1 And Int(StartTime) < Int(EndTime) Then | WkgHrs = 19.5 | Else | WkgHrs = 0 | End If | | 'First sum hour for whole days | For D = StartTime To EndTime | DOW = Weekday(D) | WkgHrs = WkgHrs + Hend(DOW) - Hstart(DOW) | Next D | | 'Now subtract time for partial days | DOW = Weekday(StartTime) '4:30 AM | Tstart = 24 * (StartTime - Int(StartTime)) | If Tstart Hstart(DOW) And Hstart(DOW) < 0 Then | WkgHrs = WkgHrs - (Tstart - Hstart(DOW)) | End If | | DOW = Weekday(EndTime) | Tend = 24 * (EndTime - Int(EndTime)) | If Tend < Hend(DOW) And Hend(DOW) < 24 Then | WkgHrs = WkgHrs - (Hend(DOW) - Tend) | End If | | End Function | | Here is my problem. I have two dates; 8/9/2007 17:37 and 8/10/2007 | 9:35. I thought, according to my code that this woudl produce a | result of 11.46 hours. The result that it is producing is 24.97. Can | anyone help me with this? | Thanks, | Jay | |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
UDF To Calculate Work hours between two dates Problem with cod
On Aug 13, 12:22 pm, Toppers
wrote: try: That worked awesome! All of my numbers are coming out correctly. The differene that I see is a change by taking out the IF statments that I have in relation to wkghrs. Thank you! Jay |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
UDF To Calculate Work hours between two dates Problem with code
The first time 8/9/2007 17:37 is 6 hours 23 minutes away from midnight. The
second time 8/10/2007 9:35 is 9 hours 35 minutes after midnight BUT the first 4.5 hours of it are not work hours; so, only 5 hours 5 minutes of the difference is work hours. Add the two up 6:23 + 5:05 equals 11 hours 28 minutes which is 11.46667 hours. Rick "Niek Otten" wrote in message ... < I thought, according to my code that this woudl produce a result of 11.46 hours Why? -- Kind regards, Niek Otten Microsoft MVP - Excel "jlclyde" wrote in message ups.com... | Here is my code. | Function WkgHrs(StartTime As Date, EndTime As Date) As Single | 'This function calculates the number of working hours between | 'two date-time values. Working hours are defined as Mon - Thurs, | '0430 - 2399.99 and Friday 0430-1030 hours. Fractions of hours are | 'included in the calculations. | Dim Hstart As Variant 'Starting hour array | Dim Hend As Variant 'Ending hour array | Dim DOW As Integer 'Day of week (1=Sunday, 2=Monday, 3=Tuesday, etc.) | Dim DOWstart As Integer | Dim DOWend As Integer | Dim D As Date | Dim DeltaH As Single 'Hours to be subtracted | Dim Tend As Single | Dim Tstart As Single | | Hstart = Array(0, 0, 4.5, 4.5, 4.5, 4.5, 4.5, 0) | Hend = Array(0, 0, 23.99999, 23.99999, 23.99999, 23.99999, 10.5, 0) | | If EndTime - StartTime < 1 And Int(StartTime) < Int(EndTime) Then | WkgHrs = 19.5 | Else | WkgHrs = 0 | End If | | 'First sum hour for whole days | For D = StartTime To EndTime | DOW = Weekday(D) | WkgHrs = WkgHrs + Hend(DOW) - Hstart(DOW) | Next D | | 'Now subtract time for partial days | DOW = Weekday(StartTime) '4:30 AM | Tstart = 24 * (StartTime - Int(StartTime)) | If Tstart Hstart(DOW) And Hstart(DOW) < 0 Then | WkgHrs = WkgHrs - (Tstart - Hstart(DOW)) | End If | | DOW = Weekday(EndTime) | Tend = 24 * (EndTime - Int(EndTime)) | If Tend < Hend(DOW) And Hend(DOW) < 24 Then | WkgHrs = WkgHrs - (Hend(DOW) - Tend) | End If | | End Function | | Here is my problem. I have two dates; 8/9/2007 17:37 and 8/10/2007 | 9:35. I thought, according to my code that this woudl produce a | result of 11.46 hours. The result that it is producing is 24.97. Can | anyone help me with this? | Thanks, | Jay | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I calculate dates and times, w/answer of days & hours | Excel Worksheet Functions | |||
| How do you subtract time as in a timecard to calculate hours work | Excel Worksheet Functions | |||
| calculate hours on work sheet | Excel Worksheet Functions | |||
| Calculate work hours between two dates | Excel Worksheet Functions | |||
| How do I calculate difference in days & hours between two dates e. | Excel Worksheet Functions | |||
Hybrid Mode
