I want 2 attendance tracker one with monthly attendance (absent/ present/ half day) with 2 paid holidays every month and another one weekly attandance for labours payout every sun on attandance. In weekly there three salary slabs. Need salary to be paid

Question

I want 2 attendance tracker one with monthly attendance (absent/ present/ half day) with 2 paid holidays every month and another one weekly attandance for labours payout every sun on attandance. In weekly there three salary slabs. Need salary to be paid

Ramesh Garg 0

User's image

In Column AJ, need how much salary to be paid at the end of month. Also to handle half day User's image

In weekly I need output in another sheet where sunday date is monetioned. I need to know how much attendance and how much salary. to select any sunday of choice.Three different salary slabs DA240/DA220/DA200, for example ity could be 500/400/300

Ashish Mathur 101.8K Reputation points Volunteer Moderator

2026-04-18T23:39:31.63+00:00

Hi,

Share the download link of the MS Excel file and show the expected result very clearly.

4 answers

Your answer

Ashish Mathur 101.8K Reputation points Volunteer Moderator

2026-04-18T23:39:31.63+00:00

Hi,

Share the download link of the MS Excel file and show the expected result very clearly.

Answer 1

AI answer

For the monthly sheet and weekly sheet, use formulas that count each attendance code and then multiply by the correct daily rate. The context only provides general Excel guidance, so only a pattern can be given, not exact cell addresses for every column.

1. Monthly attendance – salary in AJ

Assumptions (adapt as needed):

Column C = Monthly salary.
Row 3 = first employee.
Attendance for the month is in E3:AI3.
Codes: P = full day present, A = absent, 0.5P (or P0.5) = half day present.
Column D holds the number of paid leave days (already allowed and not to be deducted).

Steps:

Compute the number of working days in the row (present + half‑day + absent):
```
   =COUNTA(E3:AI3)
```
Compute present days (full + half):
```
   =COUNTIF(E3:AI3,"P") + 0.5*COUNTIF(E3:AI3,"0.5P")
```
(Change the text in the second COUNTIF to whatever is actually used for half day.)
Daily rate from monthly salary:
```
   =C3 / (COUNTA(E3:AI3))
```

Salary to be paid in AJ3 (including paid leaves in D3, excluding unpaid absences):

   = (C3 / COUNTA(E3:AI3)) * 
     ( COUNTIF(E3:AI3,"P") + 0.5*COUNTIF(E3:AI3,"0.5P") + D3 )

Copy AJ3 down for all employees.

This formula:

Divides monthly salary by the number of working days to get a per‑day rate.
Multiplies that rate by: full‑day presents + half‑day presents (0.5) + paid leaves.

2. Weekly attendance – summary by Sunday and salary slabs

Assumptions:

Weekly sheet: row 3 = first employee; dates in D2:Z2; attendance in D3:Z3.
Column C has the salary slab text: DA240, DA220, DA200.
A separate summary sheet has:
- A cell (say B1) where a Sunday date is selected.
- A table listing slabs and their daily amounts, e.g.:
  - DA240 → 500
  - DA220 → 400
  - DA200 → 300 in Summary!E2:F4.

On the weekly sheet, get the daily rate for each employee using XLOOKUP or VLOOKUP:

   =XLOOKUP(C3, Summary!E2:E4, Summary!F2:F4)

or

   =VLOOKUP(C3, Summary!E2:F4, 2, FALSE)

On the summary sheet, to count attendance for the week ending on the Sunday in B1, use SUMPRODUCT with a date range covering that week. For example, if the week is Monday–Sunday and the dates are in Weekly!D2:Z2:
```
   =SUMPRODUCT( (Weekly!$D$2:$Z$2>=B1-6) * (Weekly!$D$2:$Z$2<=B1) * (Weekly!$D$3:$Z$100="P") )
```
This returns the number of full‑day presents for all employees in that week. For half days:
```
   =0.5*SUMPRODUCT( (Weekly!$D$2:$Z$2>=B1-6) * (Weekly!$D$2:$Z$2<=B1) * (Weekly!$D$3:$Z$100="0.5P") )
```

Weekly salary for all employees for that Sunday:

   =SUMPRODUCT( (Weekly!$D$2:$Z$2>=B1-6) * (Weekly!$D$2:$Z$2<=B1) * 
                ( (Weekly!$D$3:$Z$100="P") + 0.5*(Weekly!$D$3:$Z$100="0.5P") ) *
                (XLOOKUP(Weekly!$C$3:$C$100, Summary!$E$2:$E$4, Summary!$F$2:$F$4)) )

This:

Counts each employee’s present/half‑present days in the chosen week.
Multiplies by that employee’s slab rate.
Returns total salary to be paid for that week ending on the selected Sunday.

Adjust ranges, sheet names, and half‑day codes to match the actual workbook.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Answer 2

User's image

Hi @Ramesh Garg,

(in first Sheet - see the above screenshot)

In cell AJ3 apply this formula:

=C3:C12 * (BYROW(E3:AI12, LAMBDA(a, SUM(TOCOL(XLOOKUP(a, {"P";"0.5P";"A"}, {1;0.5;0}), 3)))) + D3:D12) / DAY(EOMONTH(E2, 0))

In cell AK3 apply this formula:

=BYROW(E3:AI12, LAMBDA(a, SUM(TOCOL(XLOOKUP(a, {"P";"0.5P";"A"}, {1;0.5;0}), 3)))) + D3:D12

(in second sheet - see the below screenshot)

User's image

In cell AL2 apply this fomula:

=LET(

snms, A2:C10,

dt, TOROW(D2:AJ2, 3),

d, D3:AJ10,

ifna, IFNA(--TEXTSPLIT(TEXTJOIN(";",, BYROW(d, LAMBDA(a, ARRAYTOTEXT(TRANSPOSE(BYROW(IFNA(XLOOKUP(TEXTSPLIT(TEXTJOIN(";",, BYROW(GROUPBY(TOCOL(WEEKNUM(dt) / IF(a = "", FALSE, TRUE), 3), TOCOL(a, 3), ARRAYTOTEXT,, 0), ARRAYTOTEXT)), ", ", ";"), {"A","P05","P"}, {0,0.5,1}), 0), SUM)))))), ", ", ";"), ""),

HSTACK(snms, VSTACK("Wk - " & UNIQUE(TOROW(WEEKNUM(dt) / SIGN(BYCOL(--ISTEXT(d), SUM)), 3), TRUE), ifna), VSTACK("Total of Working Days", BYROW(ifna, SUM)))

)

In cell AN14 apply this formula:

=LET(

ms, C2:C10,

dt, D2:AJ2,

unq, UNIQUE(ms),

HSTACK(unq, VSTACK(HSTACK("Wk - " & UNIQUE(TOROW(WEEKNUM(TOROW(dt, 3)) / SIGN(BYCOL(--ISTEXT(D3:AJ10), SUM)), 3), TRUE), "Total of Working Days"), --TEXTSPLIT(TEXTJOIN(";",, BYROW(DROP(unq, 1), LAMBDA(a, ARRAYTOTEXT(BYCOL(FILTER(AO3:AR10, DROP(ms, 1) = a), SUM))))), ", ", ";")))

)

If the above formulas are not what you are looking for, please explain your problem better and show us the expected results.

Hope this helps.

IlirU

Answer 3

Dora-T 13,485 Microsoft External Staff Moderator

Hi Ramesh Garg

Based on the information you provided, I tested this on my side, and it can be achieved as follows:

1.Monthly attendance You can calculate the salary based on attendance by treating:

“P” as 1 day
“0.5P” as 0.5 day
“A” as 0 day

You can use the following formula:

=C2 - (
 MAX(0, COUNTIF(E2:AI2,"A") - D2)
 + COUNTIF(E2:AI2,"0.5P")*0.5
) * (C2/30)

In this formula:

C2 is the monthly salary
D2 is the number of paid leave days
Unpaid leave is calculated as total “A” minus paid leave
Half days (“0.5P”) are deducted proportionally

User's image

2.Weekly attendance (based on Sunday) You can calculate weekly attendance by considering:

“P” as 1 day
“P05” as 0.5 day
“A” as 0 day

First, you can identify all Sundays from the date header using:

=FILTER(D1:Z1, WEEKDAY(D1:Z1,2)=7)

Then, for each Sunday, calculate attendance for that week (Monday to Sunday) using:

=SUMPRODUCT(
((D2:Z2="P")*1 + (D2:Z2="P05")*0.5) *
(D$1:Z$1>=AA$1-6) *
(D$1:Z$1<=AA$1)
)
*
XLOOKUP($C2,$A$12:$A$14,$B$12:$B$14)

This will return the total working days for the selected week. You can then multiply the result by the corresponding daily rate to calculate the weekly salary.

User's image

You can refer to the above formulas and adjust the ranges according to your actual data.

I hope this information will help you.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Ramesh Garg 0 Reputation points

2026-04-17T14:59:05.3266667+00:00

In weekly sunday is also working and part of attendance. So salary to be calculated after sunday attendance
Dora-T 13,485 Reputation points Microsoft External Staff Moderator

2026-04-20T00:59:19.0433333+00:00
Hi Ramesh Garg

Based on your response, I understand that you want the weekly salary to be calculated only after the Sunday attendance is recorded, since Sunday is also part of the working week.

You can keep the same calculation logic (Monday to Sunday), and you can add a condition to ensure the formula only runs when the Sunday attendance is available.

For example, you may use:

=IF( COUNTIF(INDEX(F3:AB3, MATCH(AC$1,F$1:AB$1,0)),"")>0, "", SUMPRODUCT( ((F3:AB3="P")*1 + (F3:AB3="P05")*0.5) * (F$1:AB$1>=AC$1-6) * (F$1:AB$1<=AC$1) ) * XLOOKUP($C3,$A$12:$A$14,$B$12:$B$14) )

You can refer to this and adjust it based on your data to see if it matches your expected result.

Hope this helps.
Dora-T 13,485 Reputation points Microsoft External Staff Moderator

2026-04-21T02:03:03.32+00:00

Hi Ramesh Garg

Just checking in to see if you've had a chance to check my previous response.

Please let me know if there are any updates on your end.
Dora-T 13,485 Reputation points Microsoft External Staff Moderator

2026-04-22T23:13:25.1433333+00:00

Hi Ramesh Garg

Is there any update from you?

If yes, please feel free to let me know.

Answer 4

Ramesh Garg 0

I would like some simple alternate solution, if possible. Download templete option will be appreciated

0 comments

Share via

I want 2 attendance tracker one with monthly attendance (absent/ present/ half day) with 2 paid holidays every month and another one weekly attandance for labours payout every sun on attandance. In weekly there three salary slabs. Need salary to be paid

4 answers

1. Monthly attendance – salary in AJ

2. Weekly attendance – summary by Sunday and salary slabs

Your answer