Calculating shift allowance

[mwvirk]

Hi
I would really appreciate if someone could help me to calculate my shift allowance based on the attached screenshot.
Kindly note that:
working days are Sunday to Thursday
Friday & Saturday are weekend

I think to calculate the holiday rate, we can add one extra cell having P (meaning if value in the cell is P and it's still working day i.e. Sun to Thu then calculate weekend rate)
Thank you.

 

[felixstraube]

Can you show how a correctly filled row from D to V should look like? The different cases. With 4 persons, 3, and so forth. Also 1 or more person is on vacation. How many employees do you have in total that cover the shifts?

 

[mwvirk]

Can you show how a correctly filled row from D to V should look like? The different cases. With 4 persons, 3, and so forth. Also 1 or more person is on vacation. How many employees do you have in total that cover the shifts?

currently, we have 8 employees
keeping slot for 3 as "Vacant" in case we hire 1 2 or 3 more
there is no fix limit to how many employees will be in each shift but I am keeping space for 4 people in each shift.
i not sure how it is possible to create a check that if there is any name in D to V then it shouldn't be selected in any other cell in that particular row

Calculate Shift Allowance.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Type	Date	Day of The Week	Morning Shift 08:00 Hrs To 16:00 Hrs				Afternoon Shift 16:00 Hrs To 24:00 Hrs				Night Shift 00:00 Hrs To 08:00 Hrs				Off Shift				Vacation				Mike	Geroge	Peter	Alex C	Sam	Roger	Alex N	Adam	Vacant	Vacant	Vacant
2	W	01-Aug-2024	Thu	Mike	Geroge			Peter	Alex C			Sam	Roger			Alex N	Adam							the daily shift allowance should be here based on the schedule
3	W		Sat	Geroge				Peter	Alex C			Sam	Roger			Alex N	Adam			Mike
4	W			Mike	Geroge	Peter		Alex C				Sam	Roger	Alex N		Adam
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
C2	C2	=TEXT(B2,"ddd")
C3	C3	=IF(B3="",TEXT(B3,"ddd"))

Cells with Data Validation
Cell	Allow	Criteria
A2:A9	List	=$AK$1:$AL$1
D2:V9	List	=$X$1:$AH$1

 

[felixstraube]

Ok here is (what i believe) the solution to the first requirement:
We will need to use some helper columns.
In AJ3 we use this formula and draw down:

=LET(availableEmployees, TOROW($X$1:$AH$1, 1,1),
usedEmployees, IFERROR(TOROW(D2:V2,1,1), ""),
r, HSTACK(availableEmployees, usedEmployees),
IFERROR(UNIQUE(r, TRUE,TRUE), "")
)

This will give us the available employees to use in our data validation.

Then with any cell in row 2 selected we create this named range (adjusting the name of the sheet):
Name: "AvilableEmployees"

=Sheet1!$AJ2:$AT2

With cell D2 selected we create the data validation like this:

Now we copy cell D2 to all the cells where we want to select an employee, from our example D2:V4.

Now you data validation list will be updates as you select the employees for ever shift

ç

You can hide the columns AJ:AT or use another sheet for them and hide the whole sheet.

Here the working file for you to download: ShiftAllowance.zip

Let me know if this works for you and then the next requirement...

 

[mwvirk]

i am trying to use your file and then after adding any name as test, i am getting these errors.
is it becasue of different versions of Excel?
i am using Office 2016

 

[felixstraube]

Hi, Yes it is. My mistake.
Let me try and change the formula for 2016

 

[felixstraube]

Here is the version for 2016:

ShiftAllowance2.zip

Only thing that changes it the formula in column AJ.

=IFERROR(TRANSPOSE(FILTERXML("<a>"&CONCAT(IF(NOT(COUNTIF(D2:V2, $X$1:$AH$1))*($X$1:$AH$1<>""), "<b>"&$X$1:$AH$1&"</b>", ""))&"</a>","//b")), "")

Let me know if it works ok for you.

 

[mwvirk]

Here is the version for 2016:

ShiftAllowance2.zip

Only thing that changes it the formula in column AJ.

=IFERROR(TRANSPOSE(FILTERXML("<a>"&CONCAT(IF(NOT(COUNTIF(D2:V2, $X$1:$AH$1))*($X$1:$AH$1<>""), "<b>"&$X$1:$AH$1&"</b>", ""))&"</a>","//b")), "")

Let me know if it works ok for you.

i really appreciate your efforts and giving your valuable time.
there are still some issues. i am using your file and after making any change in a row by adding 1 staff, I cannot select any other in drop down. i will share the screenshots before and after:

this is how your file is after opening:

then after inserting 1 staff:
drop down is empty
and
in AJ2 to AQ2, staff names are disappreared. so there is nothing select in drop down

 

[felixstraube]

My mistake again. Didn't remember that CONCAT isn't available in 2016.
I needed to add some more helper columns:

ShiftAllowance3.zip

Hopefully now it works for you. Let me know.

 

[mwvirk]

My mistake again. Didn't remember that CONCAT isn't available in 2016.
I needed to add some more helper columns:

ShiftAllowance3.zip

Hopefully now it works for you. Let me know.

thank you. looks like it's almost done. but need to check these issues please:

 

[felixstraube]

I don't have those issues:

It is a little difficult to debug it without having the correct excel version. But I will try.

Can you show me what you see when you select cell AJ2 and then hit F2 and then F9 (having the row filled as you showed in your last post).
What I see:

After that hit ESC to go back to the formula (or if you did hit enter, just go back one step).

Do the same for AU2. What I see: