Time calculate function.

1. Senior Member
Join Date
Dec 1969
Posts
380

## Time calculate function.

I need a function to calculate standard hours and overtime hours. Can I do it with any date and time function? For example standard hours is from 9am to 6pm. I pass in a timein value and a timeout value and hope that it will calcalute the standard and overtime hours. Let say timein=8.40am and timeout=7.30pm. Any 1 seconds more should be considered 1 hour. So it should return me 3 overtime hours and 9 standard hours because 8.40am to 9am to 1 overtime hour, 9am to 6pm is 9 standard hours and 6pm to 7.30pm is 2 overtime hours. Hope someone can tell me how to achieve that

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Not too hard, I think...

&#060;%<BR>startTime = TimeValue(...)<BR>endTime = TimeValue(...)<BR><BR>overtimeHours = 0<BR>regularHours = 0<BR><BR>startRegular = TimeSerial( 9, 0, 0 )<BR>endRegular = TimeSerial( 18, 0, 0 ) &#039; 6 PM = 1800 hours<BR><BR>If startTime &#060; startRegular Then<BR>&nbsp; &nbsp; &#039; calculate the early overtime<BR>&nbsp; &nbsp; overtimeHours = ( DateDiff("s",startTime,startRegular) + 3599 ) 3600<BR>&nbsp; &nbsp; &#039; then reset startTime for regular hours calc<BR>&nbsp; &nbsp; startTime = startRegular<BR>End If<BR>If endTime &#062; endRegular Then<BR>&nbsp; &nbsp; &#039; calculate late overtime<BR>&nbsp; &nbsp; overtimeHours = overtimeHours + _<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ( DateDiff("s",endRegular,endTime) + 3599 ) 3600<BR>&nbsp; &nbsp; &#039; and again limit hours for regular hours calc<BR>&nbsp; &nbsp; endTime = endRegular<BR>End If<BR>If endTime &#062; startTime Then<BR>&nbsp; &nbsp; regularHours = ( DateDiff("s",startTime,endTime) + 3599 ) 3600<BR>End If<BR><BR>Response.Write "Regular hours: " & Regular Hours & "&#060;P&#062;"<BR>Response.Write "Overtime hours: " & Overtime Hours & "&#060;P&#062;"<BR>%&#062;<BR><BR>(p.s.: I want to work in a place where I can get an hour of overtime pay for clocking in one second early!)<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
380

## RE: not really workable

hi Bill, you got a neat code and works fine until one part it doesn&#039;t work.<BR><BR>If my StartTime="9pm" and EndTime="3am" then next morning.<BR><BR>The results shows me 0 for both regular and overtime hours.<BR>Any further help?

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Not hard to fix...

First a question, though: Are you confident enough in the data entry that, if you find and End Time that is *apparently* earlier than the Start Time that you can assume it is a time on the next day?<BR><BR>Hmmm...and we also have to worry about the case of somebody starting at 2300 and ending at 1000, don&#039;t we?<BR><BR>If so...<BR><BR>&#060;% <BR>startTime = TimeValue(...) <BR>endTime = TimeValue(...) <BR><BR>&#039; if shift ends earlier than it starts, assume it actually started prior day<BR>If endTime &#060; startTime Then<BR>&nbsp; &nbsp; startTime = DateAdd("h", -24, startTime) &#039; subtract 24 hours<BR>End If<BR><BR>overtimeHours = 0 <BR>regularHours = 0 <BR><BR>startRegular = TimeSerial( 9, 0, 0 ) <BR>endRegular = TimeSerial( 18, 0, 0 ) &#039; 6 PM = 1800 hours <BR><BR>&#039; see if the entire time is outside regular hours...<BR>If endTime &#060;= startRegular OR startTime &#062;= endRegular Then<BR>&nbsp; &nbsp; &#039; yep...either all before or all after reg hours<BR>&nbsp; &nbsp; overtimeHours = ( DateDiff("s",startTime,endTime) + 3599 ) 3600 <BR>&nbsp; &nbsp; &#039; nothing else to do in this case<BR>Else<BR>&nbsp; &nbsp; &#039; so at least part of the time is inside regular hours...<BR>&nbsp; &nbsp; If startTime &#060; startRegular Then <BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; calculate the early overtime <BR>&nbsp; &nbsp; &nbsp; &nbsp; overtimeHours = ( DateDiff("s",startTime,startRegular) + 3599 ) 3600 <BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; then reset startTime for regular hours calc <BR>&nbsp; &nbsp; &nbsp; &nbsp; startTime = startRegular <BR>&nbsp; &nbsp; End If <BR>&nbsp; &nbsp; If endTime &#062; endRegular Then <BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; calculate late overtime <BR>&nbsp; &nbsp; &nbsp; &nbsp; overtimeHours = overtimeHours + _ <BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ( DateDiff("s",endRegular,endTime) + 3599 ) 3600 <BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; and again limit hours for regular hours calc <BR>&nbsp; &nbsp; &nbsp; &nbsp; endTime = endRegular <BR>&nbsp; &nbsp; End If <BR>&nbsp; &nbsp; &#039; probably don&#039;t need the IF test here, but won&#039;t hurt:<BR>&nbsp; &nbsp; If endTime &#062; startTime Then <BR>&nbsp; &nbsp; &nbsp; &nbsp; regularHours = ( DateDiff("s",startTime,endTime) + 3599 ) 3600 <BR>&nbsp; &nbsp; End If <BR>End If<BR><BR>Response.Write "Regular hours: " & Regular Hours & "&#060;P&#062;" <BR>Response.Write "Overtime hours: " & Overtime Hours & "&#060;P&#062;" <BR>%&#062; <BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
380

## RE: ur're superb!

Wow ur&#039;re wonderful. Actually I wrote a function to calculate this regular and overtime hours and it works fine until one day the engineer clock the start hour before 9am. heehee...<BR><BR>Thanks alot again. I&#039;ll just overwrite my code with your code.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•