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.
<tbody>
</tbody>
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.
| A | B | C | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2018 c.f | 2019 balance | Jan-19 | Feb-19 | 01-Jan-19 | 02-Jan-19 | 03-Jan-19 | ||||||||||||
| 2 | ID | Name | Present | Leave | Absent | Compoff | Total | Casual L | Present | Leave | Absent | Compoff | Total | Casual L | Tue | Wed | Thu | ||
| 3 | 123 | ABC | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||
| 4 | |||||||||||||||||||
| 5 |
<tbody>
</tbody>
