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

11 replies

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.
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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?
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
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Mar 1, 2011 at 06:10 AM
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
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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

Didn't find the answer you are looking for?

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
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 18, 2011 at 02:13 PM
Add a sample file how sheet is setup
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
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
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
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
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............