Overtime calculation

Question

Hi,

Can you help me to calculate overtime in excel with two rate. The example as below:

Day	Date	In Time	Out Time	OT Hours	Total Pay ($8.89/Hour)	Rate
1.125	1.25
Fri	03/01/2020	5:00	8:00	3.00​		2​	1​
	03/01/2020	18:00	23:00	5.30​		4.3​	1​

start	End	Rate
22:00	6:00	1.25
6:00	8:00	1.125
17:00	22:00	1.125

How i want to excel automatic calculate in column highlighted in red colour.

Thanks for your help.

Answer 1

For 5.3 hours in E4 we need a start time of 17:42 (18:00 to 23:00 are 5 hours).

The formulas in G:H are done with a UDF (a macro), I:J are done with formulas, choose what you like.

Columns G:H

G3:  =(IntersectDateTime(C3,D3,$A$8,$B$8)+IntersectDateTime(C3,D3,$A$10,$B$10))*24

H3:  =(IntersectDateTime(C3,D3,$A$7,$B$7)+IntersectDateTime(C3,D3,$A$11,$B$11))*24

drag down

Code:

Function IntersectDateTime(ByVal StartTime As Date, ByVal EndTime As Date, ByVal InTime As Date, ByVal OutTime As Date) As Date

  Dim S As Date, E As Date

  If InTime >= EndTime Then Exit Function

  If OutTime <= StartTime Then Exit Function

  If InTime > StartTime Then S = InTime Else S = StartTime

  If OutTime < EndTime Then E = OutTime Else E = EndTime

  IntersectDateTime = E - S

End Function

Columns I:J

I3:  =(IF(C3>=$B$8,0,IF(D3<=$A$8,0,MIN(D3,$B$8)-MAX(C3,$A$8)))+IF(C3>=$B$10,0,IF(D3<=$A$10,0,MIN(D3,$B$10)-MAX(C3,$A$10))))*24

J3:  =(IF(C3>=$B$7,0,IF(D3<=$A$7,0,MIN(D3,$B$7)-MAX(C3,$A$7)))+IF(C3>=$B$11,0,IF(D3<=$A$11,0,MIN(D3,$B$11)-MAX(C3,$A$11))))*24

drag down

Andreas.

Answer 2

Hi Aida Ishak,

Sorry to bother you again. We are so glad to hear that you’ve solved this. To benefit other members who may encounter the same issue, I've made a summary of this issue.

Problem description:

How to automatically calculate overtime in excel with two rate like red colour as below?

Problem environment:

Office 365 for Business for Windows 10

Suggestions:

Please try what Andreas has provided:

 

For 5.3 hours in E4 we need a start time of 17:42 (18:00 to 23:00 are 5 hours).

The formulas in G:H are done with a UDF (a macro), I:J are done with formulas, choose what you like.

Columns G:H

G3:  =(IntersectDateTime(C3,D3,$A$8,$B$8)+IntersectDateTime(C3,D3,$A$10,$B$10))*24

H3:  =(IntersectDateTime(C3,D3,$A$7,$B$7)+IntersectDateTime(C3,D3,$A$11,$B$11))*24

drag down

Code:

Function IntersectDateTime(ByVal StartTime As Date, ByVal EndTime As Date, ByVal InTime As Date, ByVal OutTime As Date) As Date

  Dim S As Date, E As Date

  If InTime >= EndTime Then Exit Function

  If OutTime <= StartTime Then Exit Function

  If InTime > StartTime Then S = InTime Else S = StartTime

  If OutTime < EndTime Then E = OutTime Else E = EndTime

  IntersectDateTime = E - S

End Function

Columns I:J

I3:  =(IF(C3>=$B$8,0,IF(D3<=$A$8,0,MIN(D3,$B$8)-MAX(C3,$A$8)))+IF(C3>=$B$10,0,IF(D3<=$A$10,0,MIN(D3,$B$10)-MAX(C3,$A$10))))*24

J3:  =(IF(C3>=$B$7,0,IF(D3<=$A$7,0,MIN(D3,$B$7)-MAX(C3,$A$7)))+IF(C3>=$B$11,0,IF(D3<=$A$11,0,MIN(D3,$B$11)-MAX(C3,$A$11))))*24

drag down

Hoping this also works for someone else.

Regards,

Gloria

Answer 3

Hi Gloria,

I've tried and its works. Thanks for your help

Answer 4

Hi Andreas,

Thanks for your help. I've tried and its works.

Answer 5

Hi Aida Ishak，

Have you tried the above method? Welcome back for any updates.

Have a nice day!

Best regards,

Gloria