Calculating OT Labor hours

[davidcox9]

I've got a very specific circumstance that I hope I can explain enough to get help. I have formulas to calculate cumulative hours so we can get anything over 40 would go to OT. However if you use sick time it doesn't count towards OT. So an employee takes 8 hours sick time, then works 40 hours the rest of the week, for a total of 48 hours. There should be no OT but the formulas I have are including the OT.

Here is an example of what I have

Date	Day	Hours Type	Element Name	Hours	Cumm Hours	OT Hours
17-Sep-2023​	Sun				0​	0​
18-Sep-2023​	Mon	Sick Employee	e050 SICK PAY	8	8​	0​
19-Sep-2023​	Tue	Regular Hours	e001 REGULAR HOURS	8	16​	0​
20-Sep-2023​	Wed	Regular Hours	e001 REGULAR HOURS	12	28​	0​
21-Sep-2023​	Thu	Regular Hours	e001 REGULAR HOURS	8	36​	0​
22-Sep-2023​	Fri	Regular Hours	e001 REGULAR HOURS	12	40​	8​
23-Sep-2023​	Sat				0​	0​

I need to somehow not include the 8 hours in OT but would also need to make the Cumm Hours show 48 instead of 40.

 

[awoohaw]

try this:

Mr excel questions 63.xlsm
	A	B	C	D	E	F	G
1	Date	Day	Hours Type	Element Name	Hours	Cumm Hours	OT Hours
2	17-Sep-2023	Sun				0	0
3	18-Sep-2023	Mon	Sick Employee	e050 SICK PAY	8	8	0
4	19-Sep-2023	Tue	Regular Hours	e001 REGULAR HOURS	8	16	0
5	20-Sep-2023	Wed	Regular Hours	e001 REGULAR HOURS	12	28	0
6	21-Sep-2023	Thu	Regular Hours	e001 REGULAR HOURS	16	44	4
7	22-Sep-2023	Fri	Regular Hours	e001 REGULAR HOURS	12	56	16
8	23-Sep-2023	Sat				56	16
davidcox9

Cell Formulas
Range	Formula
F2:F8	F2	=N(F1)+E2
G2:G8	G2	=IF(IF(ISNUMBER(FIND("SICK",D2)),0,E2)+N(F1)>40,(IF(ISNUMBER(FIND("SICK",D2)),0,E2)+N(F1)-40),0)

 

[davidcox9]

The column F worked for me but column G I am getting 0,8,12,16,12. But I think I was able to change it enough to get it to work. Thank you for the help!

 

[awoohaw]

The column F worked for me but column G I am getting 0,8,12,16,12. But I think I was able to change it enough to get it to work. Thank you for the help!

what did you enter in column E in your example. Did you paste the suggestion I gave starting at cell A1?

As you can clearly see in the post I gave it works.

 

[davidcox9]

I copy and pasted your formula, then just changed to the correct cell #'s, mine aren't A1 like your example.

 

[awoohaw]

I copy and pasted your formula, then just changed to the correct cell #'s, mine aren't A1 like your example.

then you problably didn't update everything. please tell me your formulas in the top two rows of these columns: 1) Cumm Hours, 2)OT Hours.

or create an new worksheet and past the entire xl2bb I suggested in cell a1 AND compare with your numbers anew.

or tell me your column numbers and row numbers of your first row (the row with "DATE" in the first cell) and the second row (the first row of data).

or install the xl2bb add in (link below) and paste an xl2bb mini worksheet of your data.