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

You can handle both attendance requirements in Excel by keeping the attendance data separate from the salary calculation.

1. Monthly attendance tracker

Use columns such as:

Employee | Date | Status | Paid Holiday | Daily Rate | Amount

For Status, use:

• Present = 1 day
• Half Day = 0.5 day
• Absent = 0 day
• Paid Holiday = 1 paid day

Since you have 2 paid holidays per month, you can either mark those dates as Paid Holiday or keep two separate holiday cells in the monthly summary.

Your payable days can then be calculated as:

Present Days + (Half Days × 0.5) + Paid Holidays

2. Weekly labour attendance and payout

For weekly payments, keep one row per employee for each week and calculate:

Weekly Pay = Payable Days/Hours × Applicable Salary Rate

Since you have three salary slabs, you can store the three rates in a separate table and use XLOOKUP (or VLOOKUP in older Excel versions) to automatically select the appropriate rate based on the employee's slab.

For example:

Slab 1 → Rate 1 Slab 2 → Rate 2 Slab 3 → Rate 3

Then the Sunday payout can be generated automatically from the weekly attendance summary.

If your salary is based on hours rather than days, you can also convert the Excel time values into decimal hours and multiply them by the hourly rate. This makes the calculation much easier when employees work different numbers of hours.

For converting Excel time into decimal hours, you can use:

=A1*24

where A1 contains the worked time.

You can also use an hourly salary calculator to quickly verify the hourly-rate calculation before entering the final rate into your attendance sheet.

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)

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

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

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.

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. 

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