Calculating incorret time recording

[Damoo]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name

[/TD]
[TD]Org
[/TD]
[TD]Day
[/TD]
[TD]Start
[/TD]
[TD]End
[/TD]
[TD]Minutes
[/TD]
[TD]Incorrect
[/TD]
[/TR]
[TR]
[TD]Person 1
[/TD]
[TD]A
[/TD]
[TD]Monday
[/TD]
[TD]5:05 pm
[/TD]
[TD]6:00 pm
[/TD]
[TD]0:55
[/TD]
[TD]0:25
[/TD]
[/TR]
[TR]
[TD]Person 2
[/TD]
[TD]B
[/TD]
[TD]Tuesday
[/TD]
[TD]5:05 pm
[/TD]
[TD]5:15 pm
[/TD]
[TD]0:10
[/TD]
[TD]0:10
[/TD]
[/TR]
[TR]
[TD]Person 3
[/TD]
[TD]C
[/TD]
[TD]Saturday
[/TD]
[TD]6:15 am
[/TD]
[TD]2:50 pm
[/TD]
[TD]8:35
[/TD]
[TD]0:00
[/TD]
[/TR]
</tbody>[/TABLE]







Hello all,

Hoping someone can provide some assistance with creating an excel formula.

I have a whole heap of data of when people have worked overtime but I know some is incorrect and I need to capture those incorrect entries.

Basic rule is cannot work overtime between 5:30 am and 5:30 pm from Monday to Friday.

So, from the data above, Person 1 claimed 55 minutes of overtime but 25 was incorrect (between 5:30 and 5:30), person 2 claimed 10 minutes which was all incorrect, Person 3's was on Saturday so theirs is fine.

I need a formula that will calculate the incorrect minutes.

Thanks in advance for any assistance.

 

[DRSteele]

Welcome to the forum.

I mocked this up for you. Let us know if it is on track or not. Note that the Dates are formatted as Excel Time-Dates and not just text like "Monday". Note especially that this won't function for overnight shifts.

Copy F2 to I2 down as needed.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Org	Date	Start	End	OT	Claimed	Incorrect	condition
2	Person 1	A	Mon 1/07/19	17:05	18:00	0:30	0:55	0:25	over-claimed		regular start	5:30
3	Person 2	B	Tue 1/08/19	17:05	17:15	0:00	0:10	0:10	over-claimed		regular end	17:30
4	Person 3	C	Sat 1/12/19	6:15	14:50	8:35	8:35	0:00	ok
5	Person 4	C	Mon 1/14/19	3:00	11:00	2:30	0:00	2:30	underclaimed
6	Person 5	C	Sun 1/20/19	4:15	21:00	16:45	16:30	0:15	underclaimed
7	Person 6	C	Thu 1/24/19	3:00	20:15	5:15	7:15	2:00	over-claimed
Sheet3

Cell Formulas
Range	Formula
H2	=MAX(G2-F2,F2-G2)
I2	=IF(G2>F2,"over-claimed",IF(F2>G2,"underclaimed","ok"))
F2	=IF(OR(WEEKDAY(C2,2)=6,WEEKDAY(C2,2)=7),E2-D2,MAX(E2-$L$3,0)+MAX($L$2-D2,0))

[/FONT]

 

[Damoo]

Thanks so much for your help, I incorporated your suggested formulas into my workbook and everything was working swimmingly until I came across this one where the shift begins and ends outside regular times:

Start Time = 19:00
End Time =19:30

I get an OT result of 2 hours as the formula is subtracting the regular end time from the actual end time.
Then get the correct claimed time of 30 minutes and an incorrect incorrect result of 1:30 and an incorrect condition of underclaimed.

Thoughts on how to modify the formula so that those times starting and finishing outside regular times are a basic E#-D# formula?

Thanks again for your help.

Cheers

 

[DRSteele]

I mistakenly thought each workman would at least work during the regular hours during the week. So this should account for workmen who have an early AM shift or a late PM shift.

F2:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(OR(WEEKDAY(C2,2)=6,WEEKDAY(C2,2)=7,AND(D2<$L$2,E2<$L$2),AND(D2>$L$3,E2>$L$3)),E2-D2,MAX(E2-$L$3,0)+MAX($L$2-D2,0))[/FONT]