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.