Previous Topic Next Topic

Overtime Calculation

Solved/Closed
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 - Feb 17, 2011 at 09:31 AM
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 - Mar 1, 2011 at 08:32 PM
Hello Sir,

I am having problem doing my work at office.

I want to calculate the overtime hours of the employees.

A1 is Time In where i enter the time in of the employee.

Like 9:30

And B1 is Time Out where i enter the time out of the employee.

Like 17:30

In one column i want to round the time in & in other column i want to round the timeout.


Like if a person came at 9:21 i want to round it to 9:30. and if a person timed out at 17:21 it rounds to 17:30
and if a person came at 9:51 then the formula round that to 10:00 and if a person timed out at 17:51 then formula round that to 18:00.

Means employee came between Minute 21 to 50 it round to 30 if employee came at more than Minute 50 then it round to next hour.

Like if time in at 9:20 it rounds to 9:00 if time in at 9:21 it rounds to 9:30. if time in at 9:50 it rounds to 9:30 but if time in at 9:51 then it rounds to 10:00

this was one condition.

and other i want to calculate the overtime of employee like.

after 8 hours overtime started
if a person came at 9:30am on 14-Feb-2011 and he timed out at 14:30pm on 15-Feb-2011.

means after 17:30 on 14-Feb-2011 his overtime started calculate.

in this case overtime is 21 hours. Plz help


Thanks In Advance

11 responses

Answer 1 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 17, 2011 at 09:47 AM
i have two more columns for date of time in & date of timeout.

if a person came at 9:30 on 14-Feb-2011 and timed out at 18:30 on 14-Feb-2011 then formula count as 1 hour.

if timeout date is changed then it calculate till timeout date.
Answer 2 / 11
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 17, 2011 at 02:15 PM
So what if a person came at 9:40 and 9:41
does it become 9:30 and 10:00 respectively?
Answer 3 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 17, 2011 at 08:13 PM
if a person came between 9:00 to 9:20 it rounds to 9:00
if a person came between 9:21 to 9:50 it rounds to 9:30
if person came between 9:51 to 9:59, it rounds to 10:00
Answer 4 / 11
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 18, 2011 at 11:15 AM
TRY THIS

=IF(A1="","",IF(MINUTE(A1)<21,A1-TIME(0,MINUTE(A1),0),IF(MINUTE(A1)<31,A1+TIME(0,30-MINUTE(A1),0),IF(MINUTE(A1)<51,A1-TIME(0,MINUTE(A1)-30,0),A1 + TIME(0,60-MINUTE(A1),0)))))
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 27, 2011 at 04:00 PM
try this 

Public Function getHoursWorked(d1 As Range, t1 As Range, d2 As Range, t2 As Range) As Variant
   getHoursWorked = DateDiff("h", roundedDateTime(d1 + t1), roundedDateTime(d2 + t2))
End Function

Public Function roundedDateTime(dtDateTime As Date) As Date
   Select Case Minute(dtDateTime)
      Case Is < 21
         roundedDateTime = CDate(Format(dtDateTime, "yyyy-mm-dd")) + CDate(Hour(dtDateTime) & ":0:0")
      Case Is < 51
         roundedDateTime = CDate(Format(dtDateTime, "yyyy-mm-dd")) + CDate(Hour(dtDateTime) & ":30:0")
      Case Else
         If (Hour(dtDateTime) = 23) _
         Then
            roundedDateTime = CDate(Format(DateAdd("d", 1, dtDateTime), "yyyy-mm-dd")) + CDate("0:0:0")
         Else
            roundedDateTime = CDate(Format(dtDateTime, "yyyy-mm-dd")) + CDate(Hour(dtDateTime) + 1 & ":0:0")
         End If
   End Select
         
End Function
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 28, 2011 at 08:46 PM
sir where do i insert this public function ?
in the module or in the sheet.
and after inserting this code
how do it work?
because i have inserted this code to the macro but when i try to play it. it do not show any macro inserted to play

please guide me
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 1, 2011 at 06:10 AM
add them in the module
and then call them on sheet as you would call any other function. try to call this "getHoursWorked"
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 1, 2011 at 10:55 AM
Nopes,
getHoursWorked is not working
i m typing as =getHoursWorked(B1)
is this right way to call ?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 1, 2011 at 12:04 PM
Come on man, you know better than than. see the function

Public Function getHoursWorked(d1 As Range, t1 As Range, d2 As Range, t2 As Range) As Variant

it needs four parameters. d1: in date, t1, in time, d2, out date, t2 out time
Answer 5 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 18, 2011 at 12:20 PM
Yup this one is perfect to round but how to calculate the overtime.
few employees came at 9:30 Am morning & timed out at 3:00 Am. So how to calculate it.
Compulsory hours are 8 and after 8 hours their overtime start.

Means in this case overtime should be 9.30 hours..........

In overtime case if employee came at 9:30Am & Timed out at 18:00Pm then overtime formula should show as 0. because we are not allowing the employees to overtime only 30 minutes.
It should be at least 1 hour.
Means if an employee came at 9:30Am & Time out at 18:25 then overtime formula shows 1 hour after rounding.

this condition is only for overtime calculation
Rounding conditions will be of x:00 to x:50 so it should round to x:00
x:51 to x:59 so it should round to 1+x:00

Thanks In Anticipation
Answer 6 / 11
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 18, 2011 at 02:13 PM
Add a sample file how sheet is setup
Answer 7 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 18, 2011 at 08:40 PM
Okz
Answer 8 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 18, 2011 at 09:03 PM
https://authentification.site/files/26951105/Sample.xls
Answer 9 / 11
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 19, 2011 at 06:26 AM
can I come at let say at 11:52 pm ? Issue is what if due to your round off, date should change. or it will never happen
Answer 10 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 19, 2011 at 06:36 AM
yup few of employees come at night shift

they have to work minimum 8 hours and to get overtime they have to work for 9 hours so than we will give them 1 hour overtime, and after 1 hour we will round there over time in this case like:

Time In Time Out Over Time Calculation
11:30PM 9:00AM 1:30

11:30PM 8:00AM 0:00
11:30PM 8:25Am 0:00
11:30PM 8:29AM 0:00
11:30PM 8:30Am 1:00


then after getting one hour then we will round there 21 minutes into 30 & 51 minutes in to hour

Thanks
Answer 11 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 21, 2011 at 11:53 AM
I am waiting for it sir............

Similar discussions

calculating fuel surcharge
Nancy -
xpcman -

1 response
HOW TO CALCULATE AGGREGATE OF STUDENT'S MARKS
peterappiahkubi -
Wofanat -

2 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response