Calculate Roster Pattern

[Trebor8484]

Hi

Hoping someone can assist please.

I have a schedule tab which lists each employee and what they are paid each day. I am trying to find a formula that will auto fill this based on the Worked Days tab as that tells me what days each person works and their rate.

Then on the last tab I need to find the total amount that is to be paid for a specific date period for each person.

Filebin | degsqzxfsugzpyrc

Thanks

 

[ravi2628]

Hi

Hoping someone can assist please.

I have a schedule tab which lists each employee and what they are paid each day. I am trying to find a formula that will auto fill this based on the Worked Days tab as that tells me what days each person works and their rate.

Then on the last tab I need to find the total amount that is to be paid for a specific date period for each person.

Filebin | degsqzxfsugzpyrc

Thanks

Hi Trebor,

I have done the changes and uploaded the file to the same link

Filebin | degsqzxfsugzpyrc

Convenient file sharing. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

 

[Fluff]

@ravi2628 can you please post your solution to the board, as per Rule#4. Thanks

 

[ravi2628]

@ravi2628 can you please post your solution to the board, as per Rule#4. Thanks

Hi Fluff,

sorry, I understand the board rules but due to more Sheets and Formulas in the data I have uploaded in the online link shared by the user


For the users of the board, I am sharing the solution of the Sheets.

Example1.xlsx
	A	B	C	D
1	Staff Number	Surname	Team	01-10-2021
2				Friday
3	123456	EXAMPLE1	Team1	£90.00
4	123457	EXAMPLE2	Team2	£90.00
5	123458	EXAMPLE3	Team3	£90.00
6	123459	EXAMPLE4	Team4	£90.00
7	123460	EXAMPLE5	Team5	£90.00
8	123461	EXAMPLE6	Team6	£90.00
9	123462	EXAMPLE7	Team7	£175.00
10	123463	EXAMPLE8	Team8	£55.00
11	123464	EXAMPLE9	Team9	£55.00
12	123465	EXAMPLE10	Team10	£55.00
SCHEDULE

Cell Formulas
Range	Formula
D2	D2	=TEXT(D1,"DDDD")
D3:D12	D3	=IF(VLOOKUP($A3,'WORK DAYS'!$A$1:$I$11,MATCH(D$2,'WORK DAYS'!$A$1:$I$1,0))="Y",VLOOKUP($A3,'WORK DAYS'!$A$1:$I$11,2,0),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:AA12	Cell Value	=0	text	NO

Example1.xlsx
	A	B	C	D	E
1	Staff Number	Total
2	123456	£540.00		start	01-10-2021
3	123457	£540.00		end	10-10-2021
4	123458	£540.00
5	123459	£540.00
6	123460	£540.00
7	123461	£540.00
8	123462	£1,050.00
9	123463	£440.00
10	123464	£440.00
11	123465	£440.00
TOTAL AMOUNT

Cell Formulas
Range	Formula
B2:B11	B2	=SUMPRODUCT((SCHEDULE!$D$3:$AA$12)*(SCHEDULE!$D$1:$AA$1>=$E$2)*(SCHEDULE!$D$1:$AA$1<=$E$3)*(SCHEDULE!$A$3:$A$12=$A2))