VBA/Formula Help

[Scott_T_27]

Hi All

New to the forum but hopeful someone can help.

This is a wok project I have been tasked with but it has me beat.

We process payroll weekly but during overtime season it becomes a bit of a pain as our T&A system doesn't deduct a break when employees have done the required amount of overtime.
See attached image of spreadsheet that we get from T&A system
What I need is a formula or something that will check columns F to L to see if it contains 10+ hours, and then providing column E states either "Day Shift" or "Night Shift" deduct 0.25 from the appropriate cell in F to L. The reason I need it to check column E is I have people that work 10 hour weekend shifts who do not require the 0.25 deducted.

Sorry but my work PC will not let me add the image but data is arranged as follows
A Employee No., B Forename, C Surname, D Branch, E Shift, F Mon, G Tue, H Wed, I Thu, J Fri, K Sat, L Sun, M Total Hours

Thanks in advance.

Scott

 

[taigovinda]

Hi Scott,


Please provide some sample values and the expected output, then someone will be faster than me and provide a solution.


Tai

 

[Scott_T_27]

Sorry

Employees work any 5 days out of 7 on a rota basis, working a standard 7.5 hours per day. Can do up to 4 hours overtime per day so maximum would be 11.5 hours on any single day.
Weekend workers work 4 days at 10 hours per day so maximum would be 14 hours.

Thanks

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee No.	Forename	Surname	Branch	Shift	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total Hours
2					Day Shift	8	8	10	12	8	4	4	53.5
3					Night Shift	8	8	10	12	8	4	4	53.5
4						8	8	10	12	8	4	4	54
Sheet6

Cell Formulas
Range	Formula
M2	=SUM(F2:L2)-IF(OR(E2="Day Shift",E2="Night Shift"),0.25*COUNTIF(F2:L2,">=10"),0)

 

[Scott_T_27]

That worked perfectly, thank you

 

[AlanY]

you're welcome