Yearly attendance sheet_Need help

[kreeesh]

Hi All,

I want to make a yearly attendance sheet. From the below table I need the formulas for followed conditions.

1. Every month Casual leaves should be added (2 casual leaves every month) based on Month into Casual L colomn. Example: for Jan-19 K3 should be 2 based on today date.
2. 2019 balance should be added from Compoff and Casual L of each month based on ID criteria. Example: for ID 123 it should be 4 in E3(Jan-19, Feb-19 casual Leave 2 of each).
3. Jan-19 casual leaves should be zero once 2018 c.f is zero and Feb-19 casual leaves should be zero once Jan-19 casual leaves are zero. Example: Feb-19 casuals should be zero once Jan-19 balance is zero.
4. Is it possible to make any calculation should work based on ID criteria.







[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[TH]N[/TH]
[TH]O[/TH]
[TH]P[/TH]
[TH]Q[/TH]
[TH]R[/TH]
[TH]S[/TH]
[TH]T[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2018 c.f[/TD]
[TD]2019 balance[/TD]
[TD]Jan-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01-Jan-19[/TD]
[TD]02-Jan-19[/TD]
[TD]03-Jan-19[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD]Present[/TD]
[TD]Leave[/TD]
[TD]Absent[/TD]
[TD]Compoff[/TD]
[TD]Total[/TD]
[TD]Casual L[/TD]
[TD]Present[/TD]
[TD]Leave[/TD]
[TD]Absent[/TD]
[TD]Compoff[/TD]
[TD]Total[/TD]
[TD]Casual L[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123[/TD]
[TD]ABC[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]