gssachin
Board Regular
- Joined
- Nov 14, 2013
- Messages
- 155
Hi,
I want to count attendance for a specific period.
I have a 1000 employee’s timesheet. Column “C” to column “AG” for 31 days of May and specific period enter in column “AH” & “AI” i.e. start date & end date
I received attendance in text format to I use to the following formula (in cell “AJ” to get full month attendance.
=(COUNTIF($C$2:$AG$2,"8")*8)+(COUNTIF($C$2:$AG$2,"6")*8)+(COUNTIF($C$2:$AG$2,"5")*5)+(COUNTIF($C$2:$AG$2,"3")*3)
But I need to get attendance for the Specific period only…
Any suggestion/help…..
I want to count attendance for a specific period.
I have a 1000 employee’s timesheet. Column “C” to column “AG” for 31 days of May and specific period enter in column “AH” & “AI” i.e. start date & end date
I received attendance in text format to I use to the following formula (in cell “AJ” to get full month attendance.
=(COUNTIF($C$2:$AG$2,"8")*8)+(COUNTIF($C$2:$AG$2,"6")*8)+(COUNTIF($C$2:$AG$2,"5")*5)+(COUNTIF($C$2:$AG$2,"3")*3)
But I need to get attendance for the Specific period only…
Book1 | ||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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 | AI | AJ | |||
1 | EMP NO | NAME | 01 | 02 | # | # | # | # | # | # | 09 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | START DATE | END DATE | WORKED HRS | ||
2 | 100 | ABC | WO | 6 | 6 | 6 | 6 | 6 | 6 | 6 | WO | 6 | 6 | 6 | 6 | 6 | WO | 6 | 6 | 6 | 6 | 6 | 6 | 6 | WO | H | H | H | O | O | WO | O | O | 10/05/2020 | 26/05/2020 | 96 | ||
3 | 101 | CDE | 6 | WO | 6 | 6 | 6 | 6 | 6 | 6 | WO | 6 | 6 | 6 | 6 | 6 | 6 | WO | 6 | 6 | 6 | 6 | 6 | 6 | WO | H | H | H | 8 | 8 | 8 | WO | 8 | 01/05/2020 | 31/05/2020 | 184 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AJ3 | AJ3 | =(COUNTIF($C$3:$AG$3,"8")*8)+(COUNTIF($C$3:$AG$3,"6")*8)+(COUNTIF($C$3:$AG$3,"5")*5)+(COUNTIF($C$3:$AG$3,"3")*3) |
Any suggestion/help…..