Complete month attendance allowance

[Zubair]

Hi Experts,

I have a 37 Column month wise structure and in last column count of P.

I want to add Sunday as well if full month P is available during working days as Sunday is holiday.

Like in July
AAA = 27 + 4 (Sun) = 31 (Full month attendance)
BBB = 26 + 0 = 26 (1 Absent during the month)

FC1

July 2021

Employee Name

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

27

28

29

30

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

1

2

AAA

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

27

BBB

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

A

26

In August
AAA = 26 + 5 (Sun) = 31 (Full month attendance)
BBB = 24 + 0 = 24 (2 Absents during the month)

FC1

August 2021

Employee Name

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

1

2

3

4

5

6

AAA

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

26

BBB

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

A

A

24

 

[hrayani]

To count sundays between 2 dates

TEST FILE.xlsm
	A
1	01-Jul
2	31-Jul
3	4
Sheet5

Cell Formulas
Range	Formula
A3	A3	=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=1)*1)

Add this to your current formula

 

[Zubair]

Hi hrayani - many thanks its working in case of full attendance of the month, how can I exclude if any day a person is absent during the month in case of BBB?

 

[hrayani]

TEST FILE.xlsm
	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
1	FC1	July 2021
2	Employee Name	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat
3	Zubair	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P	31
4	Humayun	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	A	26
5
6	FC1	August 2021
7	Employee Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
8	Zubair		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	31
9	Humayun		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		A	A	24
Sheet5

Cell Formulas
Range	Formula
AG3:AG4	AG3	=IF(COUNTIF(B3:AF3,"A")>0,COUNTIF(B3:AF3,"P"),COUNTIF(B3:AF3,"P")+SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B$2&":"&$AF$2)))=1)*1))
AG8:AG9	AG8	=IF(COUNTIF(B8:AF8,"A")>0,COUNTIF(B8:AF8,"P"),COUNTIF(B8:AF8,"P")+SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B$7&":"&$AF$7)))=1)*1))

 

[Zubair]

Excellent hrayani many thanks, 1 more request please

I am applying the above formula in all months and in Sep for example without A or P its showing 4, how can I amend formula to show blank in total column if the month not started ?

FC1	September 2021
Employee Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
	29	30	31	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1	2	3	4
Zubair																																						4
Humayun																																						4
Azeem																																						4

 

[hrayani]

TEST FILE.xlsm
	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
1	FC1	July 2021
2	Employee Name	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat
3	Zubair	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P	31
4	Humayun	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	A	26
5
6	FC1	August 2021
7	Employee Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
8	Zubair		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	31
9	Humayun		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		A	A	24
Sheet5

Cell Formulas
Range	Formula
AG3:AG4	AG3	=IF(AND(COUNTIF(B3:AF3,"P")=0,COUNTIF(B3:AF3,"A")=0),"",IF(COUNTIF(B3:AF3,"A")>0,COUNTIF(B3:AF3,"P"),COUNTIF(B3:AF3,"P")+SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B$2&":"&$AF$2)))=1)*1)))
AG8:AG9	AG8	=IF(AND(COUNTIF(B8:AF8,"P")=0,COUNTIF(B8:AF8,"A")=0),"",IF(COUNTIF(B8:AF8,"A")>0,COUNTIF(B8:AF8,"P"),COUNTIF(B8:AF8,"P")+SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B$7&":"&$AF$7)))=1)*1)))

 

[Zubair]

Many thanks all requirements covered in your given formula, Jazakallah Khair

 

[hrayani]

Many thanks all requirements covered in your given formula

Pleasure

Jazakallah Khair

Ameen

 

[Zubair]

1 additional request, F = Half day, needs to be added in this formula, means if user select F the formula shall count 0.5

 

[hrayani]

1 additional request, F = Half day, needs to be added in this formula, means if user select F the formula shall count 0.5

See if this helps

TEST FILE.xlsm
	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
1	FC1	Jul-21
2	Employee Name	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat
3	Zubair	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	F	F	F	P	29.50
4	Humayun	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		F	F	F	P	P	A	24.50
5
6	FC1	Aug-21
7	Employee Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
8	Zubair		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		F	F	P	P	F	P		P	P	29.50
9	Humayun		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	P	P		P	P	P	P	F	P		A	A	23.50
Sheet5

Cell Formulas
Range	Formula
AG3:AG4	AG3	=IF(AND(COUNTIF(B3:AF3,"P")=0,COUNTIF(B3:AF3,"A")=0),"",IF(COUNTIF(B3:AF3,"A")>0,COUNTIF(B3:AF3,"P")+COUNTIF(B3:AF3,"F")/2,COUNTIF(B3:AF3,"P")+COUNTIF(B3:AF3,"F")/2+SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B$2&":"&$AF$2)))=1)*1)))
AG8:AG9	AG8	=IF(AND(COUNTIF(B8:AF8,"P")=0,COUNTIF(B8:AF8,"A")=0),"",IF(COUNTIF(B8:AF8,"A")>0,COUNTIF(B8:AF8,"P")+COUNTIF(B8:AF8,"F")/2,COUNTIF(B8:AF8,"P")+COUNTIF(B8:AF8,"F")/2+SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B$7&":"&$AF$7)))=1)*1)))