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.

 

[mwvirk]

I don't have those issues:

View attachment 116433

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:

View attachment 116434

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:

View attachment 116435

from AJ2

from AJ3

 

[mwvirk]

and below is from AU2

 

[felixstraube]

I don't get it, you should have just one Adam in your list in AJ2:AT2.

I don't know how to debug it without having the version of excel.
Maybe we could have a simple dropdown with all the names, and we mark it with a red background if it is duplicated. Would that work?

 

[mwvirk]

I don't get it, you should have just one Adam in your list in AJ2:AT2.

I don't know how to debug it without having the version of excel.
Maybe we could have a simple dropdown with all the names, and we mark it with a red background if it is duplicated. Would that work?

Maybe we could have a simple dropdown with all the names, and we mark it with a red background if it is duplicated. Would that work?


yes. that will be OK as long as there is indication of duplicate entry.

 

[felixstraube]

Ok.
here is the file for you to test:

ShiftAllowance4.zip

ShiftAllowance.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	George	Peter	Alex C	Sam	Roger	Alex N	Adam
2	W	01/08/2024	Thu	Mike	George	Roger	Adam		Peter	Alex C										Alex N	Sam
3	W		Sat	Mike	Mike
4	W				George	George		Sam	Sam
5
Sheet1

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:V4	Expression	=SUM(($D2:$V2=D2)*(D2<>""))>1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:V4	List	=Employees

Just one named range, and one rule for CF.

 

[mwvirk]

Ok.
here is the file for you to test:

ShiftAllowance4.zip

ShiftAllowance.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	George	Peter	Alex C	Sam	Roger	Alex N	Adam
2	W	01/08/2024	Thu	Mike	George	Roger	Adam		Peter	Alex C										Alex N	Sam
3	W		Sat	Mike	Mike
4	W				George	George		Sam	Sam
5
Sheet1

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:V4	Expression	=SUM(($D2:$V2=D2)*(D2<>""))>1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:V4	List	=Employees

Just one named range, and one rule for CF.

I applied your format and it seems it is working as per requirement. now before going for the final calculation about the shift allowance, I need another check.
I am trying to count the total employees and then put the count at the end of each row. once I add any employee to the shift, this count should decrease and finally end at 0
This can help me know the total heads and ensure all are on the shift. I am sharing the picture.
i know that I have 8 employees but I cannot count so manually putting 8 in W
whenever I assign 1 person in the shift, this count should decrease and when all 8 are in the shifts, it will show me 0 to know all heads are used.

ShiftAllowance (v2016) 3.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	George	Peter	Alex C	Sam	Roger	Alex N	Adam
2	W	01-10-2024	Tue
3	W	02-10-2024	Wed
4	W	03-10-2024	Thu																					0
5	W	04-10-2024	Fri
6	W	05-10-2024	Sat
7	W	06-10-2024	Sun
8	W	07-10-2024	Mon
9	W	08-10-2024	Tue
10	W	09-10-2024	Wed
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IF(MONTH(DATE($B$35,$A$35,1)+ROWS($A$1:$A1)-1)=$A$35,DATE($B$35,$A$35,1)+ROWS($A$1:$A1)-1,"")
C2:C10	C2	=TEXT(B2,"ddd")
X4	X4	=COUNT(X:AH)

Named Ranges
Name	Refers To	Cells
Employees	=Sheet1!$X$1:$AH$1	X4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C31	Cell Value	contains "Fri"	text	NO
C2:C31	Cell Value	contains "Sat"	text	NO
D2:V31	Expression	=SUM(($D2:$V2=D2)*(D2<>""))>1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:V10	List	=Employees

 

[felixstraube]

Would this work?:
in cell W2:

=8-COUNTA(D2:V2)

And drag down.

 

[mwvirk]

Would this work?:
in cell W2:

=8-COUNTA(D2:V2)

And drag down.

It can work but if I hard code 8 then I don't have control when I add any new employee or when someone resign.
I was thinking if we can count X1:AH1 to calculate names?