Index, Match, Counta formula for excel - Stack Overflow

admin管理员组

文章数量:1027910

Worksheet design:

Data validation list:

I created an automated duty roster for my work. It is a duty circle from top to bottom. daily basis, emp 1 to emp last.

The table columns are:
B= serial no, C= Badge no, D= Name, E= Leave staring date, F= Ending date, G= Leave Status, H= Weekly Holiday (all employee 6 days work and one rest),I to AM = days of months (1 - 31)

also

Cells I6:AM6 are data validation list for starting duty each days
Cells I7:AM7 are days ( Sun, Mon,... etc)
Cells I8:AM8 are dates ( 1,2,3,..... etc)

I already created working formulas for the first three employees:

(=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E10, I$8<=$F10), $G10, IF(AND($H10=I$7, $H10<>""), "Off",          IF($I9="", I$6,INDEX(Duty, IF(MATCH(I$9, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$9, Duty, 0) + 1))))))

=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E11, I$8<=$F11), $G11, IF(AND($H11=I$7, $H11<>""), "Off",          IF(I$10="L",IF($I$6="", INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), IF(I10="Off", IF($I$6="",INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)))))))

=IF(OR(I$6="", I$8=""), "",IF(AND(I$8>=$E12, I$8<=$F12), $G12, IF(AND($H12=I$7, $H12<>""), "Off", IF(OR(I$10="L", I$10="Off"), IF(OR(I$11="L", I$11="Off"), INDEX(Duty, 1), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1))), IF(OR(I$11="L", I$11="Off"),                INDEX(Duty, IF(MATCH(I$10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$10, Duty, 0) + 1)), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1)))))))

Now I need to create formula for next row. The above employees should be happen various things. it mean Leave (L), Duty off (Off).

If first day 1st employee may be L, second employee duty, third employee off then fourth employee should be duty (duty circle )

second day 1st employee duty, second employee off, third emp leave., etc.

Anyone have any idea of this formula or any suggestions?

Worksheet design:

Data validation list:

I created an automated duty roster for my work. It is a duty circle from top to bottom. daily basis, emp 1 to emp last.

The table columns are:
B= serial no, C= Badge no, D= Name, E= Leave staring date, F= Ending date, G= Leave Status, H= Weekly Holiday (all employee 6 days work and one rest),I to AM = days of months (1 - 31)

also

Cells I6:AM6 are data validation list for starting duty each days
Cells I7:AM7 are days ( Sun, Mon,... etc)
Cells I8:AM8 are dates ( 1,2,3,..... etc)

I already created working formulas for the first three employees:

(=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E10, I$8<=$F10), $G10, IF(AND($H10=I$7, $H10<>""), "Off",          IF($I9="", I$6,INDEX(Duty, IF(MATCH(I$9, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$9, Duty, 0) + 1))))))

=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E11, I$8<=$F11), $G11, IF(AND($H11=I$7, $H11<>""), "Off",          IF(I$10="L",IF($I$6="", INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), IF(I10="Off", IF($I$6="",INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)))))))

=IF(OR(I$6="", I$8=""), "",IF(AND(I$8>=$E12, I$8<=$F12), $G12, IF(AND($H12=I$7, $H12<>""), "Off", IF(OR(I$10="L", I$10="Off"), IF(OR(I$11="L", I$11="Off"), INDEX(Duty, 1), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1))), IF(OR(I$11="L", I$11="Off"),                INDEX(Duty, IF(MATCH(I$10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$10, Duty, 0) + 1)), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1)))))))

Now I need to create formula for next row. The above employees should be happen various things. it mean Leave (L), Duty off (Off).

If first day 1st employee may be L, second employee duty, third employee off then fourth employee should be duty (duty circle )

second day 1st employee duty, second employee off, third emp leave., etc.

Anyone have any idea of this formula or any suggestions?

• excel
• excel-formula

Share Improve this question Follow edited Dec 29, 2024 at 21:41 TylerH 21.2k7676 gold badges7979 silver badges110110 bronze badges asked Dec 29, 2024 at 17:45 LokeshLokesh 1133 bronze badges New contributor Lokesh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

So basically what you want to do is alternate between leave days (L), duty off (Off), and duty in. That's going to require dynamic states and changing states for each employee.

What we need:

• Leave Days (L): Employees may be on leave based on the date range specified in their columns.
• Off Days (Off): Employees will have an "Off" day if it matches their weekly time off
• Duty: For the other days, employees should be on duty, and the formula should handle the rotation.

Acceptance Criteria:

• Account for multiple employees on leave, duty, or off.
• Example Monady: Employee 1 is on leave (L), Employee 2 is on duty, Employee 3 is off.
• Check if employee is within the leave period (this is accomplished by checking leave),
• Check if it's part of weekend,

Here’s a formula structure that you can use for the 4th employee (you can adapt it for subsequent rows by modifying the employee in question with their reference):

=IF(OR(I$6="", I$8=""), "", 
    IF(AND(I$8>=$E14, I$8<=$F14), $G14,  // Check if within leave dates
    IF(AND($H14=I$7, $H14<>""), "Off",  // Check if it's a weekly off time
    IF(MOD(ROW(), 3) = 1, "L",   // Leave (every 3 rows)
    IF(MOD(ROW(), 3) = 2, "Off",   // Off (every 3 rows)
    "Duty"   // Default
    )))))

Explanation:

• The formula checks whether the date (I$8) is within the employee's leave period (E14 to F14).
• If yes, it returns the leave status ($G14).
• Weekly Holiday Check: If the employee's weekly holiday matches the day (I$7), it will return "Off".
• MOD(ROW(), 3) ensures the rotation assigns the correctt label indicating type of time off.
• Defaults to "Duty" on a day that they're supposed to be at work.
• ROW() function ensures the pattern alternates every 3 rows across employees.

=IF(OR(I$6="", I$8=""), "", 
    IF(AND(I$8>=$E14, I$8<=$F14), $G14, 
    IF(AND($H14=I$7, $H14<>""), "Off", 
    IF(MOD(ROW(), 3) = 1, "L", 
    IF(MOD(ROW(), 3) = 2, "Off", 
    "Duty")))))

Worksheet design:

Data validation list:

I created an automated duty roster for my work. It is a duty circle from top to bottom. daily basis, emp 1 to emp last.

The table columns are:
B= serial no, C= Badge no, D= Name, E= Leave staring date, F= Ending date, G= Leave Status, H= Weekly Holiday (all employee 6 days work and one rest),I to AM = days of months (1 - 31)

also

Cells I6:AM6 are data validation list for starting duty each days
Cells I7:AM7 are days ( Sun, Mon,... etc)
Cells I8:AM8 are dates ( 1,2,3,..... etc)

I already created working formulas for the first three employees:

(=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E10, I$8<=$F10), $G10, IF(AND($H10=I$7, $H10<>""), "Off",          IF($I9="", I$6,INDEX(Duty, IF(MATCH(I$9, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$9, Duty, 0) + 1))))))

=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E11, I$8<=$F11), $G11, IF(AND($H11=I$7, $H11<>""), "Off",          IF(I$10="L",IF($I$6="", INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), IF(I10="Off", IF($I$6="",INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)))))))

=IF(OR(I$6="", I$8=""), "",IF(AND(I$8>=$E12, I$8<=$F12), $G12, IF(AND($H12=I$7, $H12<>""), "Off", IF(OR(I$10="L", I$10="Off"), IF(OR(I$11="L", I$11="Off"), INDEX(Duty, 1), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1))), IF(OR(I$11="L", I$11="Off"),                INDEX(Duty, IF(MATCH(I$10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$10, Duty, 0) + 1)), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1)))))))

Now I need to create formula for next row. The above employees should be happen various things. it mean Leave (L), Duty off (Off).

If first day 1st employee may be L, second employee duty, third employee off then fourth employee should be duty (duty circle )

second day 1st employee duty, second employee off, third emp leave., etc.

Anyone have any idea of this formula or any suggestions?

Worksheet design:

Data validation list:

I created an automated duty roster for my work. It is a duty circle from top to bottom. daily basis, emp 1 to emp last.

The table columns are:
B= serial no, C= Badge no, D= Name, E= Leave staring date, F= Ending date, G= Leave Status, H= Weekly Holiday (all employee 6 days work and one rest),I to AM = days of months (1 - 31)

also

Cells I6:AM6 are data validation list for starting duty each days
Cells I7:AM7 are days ( Sun, Mon,... etc)
Cells I8:AM8 are dates ( 1,2,3,..... etc)

I already created working formulas for the first three employees:

(=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E10, I$8<=$F10), $G10, IF(AND($H10=I$7, $H10<>""), "Off",          IF($I9="", I$6,INDEX(Duty, IF(MATCH(I$9, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$9, Duty, 0) + 1))))))

=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E11, I$8<=$F11), $G11, IF(AND($H11=I$7, $H11<>""), "Off",          IF(I$10="L",IF($I$6="", INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), IF(I10="Off", IF($I$6="",INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)))))))

=IF(OR(I$6="", I$8=""), "",IF(AND(I$8>=$E12, I$8<=$F12), $G12, IF(AND($H12=I$7, $H12<>""), "Off", IF(OR(I$10="L", I$10="Off"), IF(OR(I$11="L", I$11="Off"), INDEX(Duty, 1), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1))), IF(OR(I$11="L", I$11="Off"),                INDEX(Duty, IF(MATCH(I$10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$10, Duty, 0) + 1)), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1)))))))

Now I need to create formula for next row. The above employees should be happen various things. it mean Leave (L), Duty off (Off).

If first day 1st employee may be L, second employee duty, third employee off then fourth employee should be duty (duty circle )

second day 1st employee duty, second employee off, third emp leave., etc.

Anyone have any idea of this formula or any suggestions?

• excel
• excel-formula

Share Improve this question Follow edited Dec 29, 2024 at 21:41 TylerH 21.2k7676 gold badges7979 silver badges110110 bronze badges asked Dec 29, 2024 at 17:45 LokeshLokesh 1133 bronze badges New contributor Lokesh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

So basically what you want to do is alternate between leave days (L), duty off (Off), and duty in. That's going to require dynamic states and changing states for each employee.

What we need:

• Leave Days (L): Employees may be on leave based on the date range specified in their columns.
• Off Days (Off): Employees will have an "Off" day if it matches their weekly time off
• Duty: For the other days, employees should be on duty, and the formula should handle the rotation.

Acceptance Criteria:

• Account for multiple employees on leave, duty, or off.
• Example Monady: Employee 1 is on leave (L), Employee 2 is on duty, Employee 3 is off.
• Check if employee is within the leave period (this is accomplished by checking leave),
• Check if it's part of weekend,

Here’s a formula structure that you can use for the 4th employee (you can adapt it for subsequent rows by modifying the employee in question with their reference):

=IF(OR(I$6="", I$8=""), "", 
    IF(AND(I$8>=$E14, I$8<=$F14), $G14,  // Check if within leave dates
    IF(AND($H14=I$7, $H14<>""), "Off",  // Check if it's a weekly off time
    IF(MOD(ROW(), 3) = 1, "L",   // Leave (every 3 rows)
    IF(MOD(ROW(), 3) = 2, "Off",   // Off (every 3 rows)
    "Duty"   // Default
    )))))

Explanation:

• The formula checks whether the date (I$8) is within the employee's leave period (E14 to F14).
• If yes, it returns the leave status ($G14).
• Weekly Holiday Check: If the employee's weekly holiday matches the day (I$7), it will return "Off".
• MOD(ROW(), 3) ensures the rotation assigns the correctt label indicating type of time off.
• Defaults to "Duty" on a day that they're supposed to be at work.
• ROW() function ensures the pattern alternates every 3 rows across employees.

=IF(OR(I$6="", I$8=""), "", 
    IF(AND(I$8>=$E14, I$8<=$F14), $G14, 
    IF(AND($H14=I$7, $H14<>""), "Off", 
    IF(MOD(ROW(), 3) = 1, "L", 
    IF(MOD(ROW(), 3) = 2, "Off", 
    "Duty")))))

本文标签： INDEXmatchCounta formula for excelStack Overflow