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]

Could you explain a little more in detail what you need? I'm not quite sure what you need or how you results should look like

 

[mwvirk]

Could you explain a little more in detail what you need? I'm not quite sure what you need or how you results should look like

I'm working in rotating shifts for the full month.
I have different shift allowances as shown in the picture under 1st post.
I really don't know how to start any formulas as I only have a blank sheet to add name.
There are different rates...

If I am in morning shift then rate / hour is different. Similary different in afternoon then night and nothing will be paid during off shift.

Same time if I'm in any shift during Fri & Sat then the rate/hour will ve different and if I'm working during public holiday no matter what day it is, rate will apply as per the picture shown in 1st post.

I am trying to create a blank sheet for the entire team so everyone's total will be calculated (picture attached)

I am using data validation to get the list of all colleagues. So I need a check that if 1 staff is already assigned in a shift then his name should not be accepted on same day.

 

[mwvirk]

BTW the calculated amount for each staff (total 8) during a day will appear in U, V, W, X, Y, Z, AA, AB

 

[felixstraube]

It is still not clear what you need to me.

1. What is the result you are looking for? A total of allowance per employee? What is the period of time in which you want to add them up? A week, a month, just for one day?

2. You gave a list of employees, but didn't say what shift they work on. We would need that or the starting and ending time of their working time.

3. As I understand you correctly, for holidays and weekends you will apply the same rates. Is that correct?

4. Can you give a sample of what you data will look like? How would you fill the table you took photos of. Can you please use XL2BB or clear screenshot (not a photo) (you can use the key Print Scr, or Win+Shift+S).

 

[mwvirk]

It is still not clear what you need to me.

1. What is the result you are looking for? A total of allowance per employee? What is the period of time in which you want to add them up? A week, a month, just for one day?

2. You gave a list of employees, but didn't say what shift they work on. We would need that or the starting and ending time of their working time.

3. As I understand you correctly, for holidays and weekends you will apply the same rates. Is that correct?

4. Can you give a sample of what you data will look like? How would you fill the table you took photos of. Can you please use XL2BB or clear screenshot (not a photo) (you can use the key Print Scr, or Win+Shift+S).

sorry I was posting earlier using my mobile and XL2BB was not allowed on the office computer. here are the details. thank you.

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
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					Mike	Geroge	Peter	Alex C	Sam	Roger	Alex N	Adam
2	W	01-Aug-2024	Thu																		the daily shift allowance should be here based on the schedule
3			Sat
4
5
6
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:A6	List	=$AE$1:$AF$1
D2:S6	List	=$AD$2:$AD$9

 

[felixstraube]

You didn't answer my questions.

 

[mwvirk]

You didn't answer my questions.

I am very sorry for the late reply. I was still trying to get the full details from HR.
please ignore the earlier post. here I am sharing the updated sheet/requirement by adding a new column "Vacation" for the future if there is any new hiring or need for additional staff then replace the name in the "Vacant" cell.

if possible, let us try to resolve it step by step.

my first requirement is that 1 staff shouldn't be in multiple shifts and also the one who's on vacation shouldn't be in any shift: (Morning/Afternoon/Night/Off)
e.g. if Mike is selected for the morning shift then his name shouldn't be selected in any other cell from D2 to V2 for 01-Aug-2024

once we are done with this, I will start calculating the shift allowance based on the calculation provided in post #1

Thank you.

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																					the daily shift allowance should be here based on the schedule
3			Sat
4
5
6
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:A6	List	=$AK$1:$AL$1
D2:V6	List	=$X$1:$AH$1

 

[felixstraube]

I don't understand you first requirement. Do you want that a formula fills automatically the rows 2 onwards? With the names of the staff? What result are you expecting?
You also didn't answer my 4 questions from post #5.

 

[mwvirk]

thank you.
4th question will be discussed in Step 2


I am using the dropdown in D to V

I am expecting max 4 and min 1 person in the morning shift, the same in the Afternoon and night, and off-shift
In vacation, I am expecting that 1 or 2 or 3 staff will be on vacation at any given time.

my requirement in step 1 is: if Mike is working in morning shift then he shouldn't be assigned to any other shift. it's like stopping the same person from working multiple shifts which is of course not possible or fair
so if Mike working in morning shift then from D to V his name shouldn't be duplicated and then the same applies to all other staff.