What formula to use in excel to calculate leave balance based on a pro-rata basis if the joining data falls in between the month. Eg: 22/07/21
From India, Bengaluru
Acknowledge(0)
Amend(0)
From India, Bengaluru
Acknowledge(0)Amend(0)
Everything in any process is not required to be done by a formula in Excel. There should be some intuition based on common sense.
Let us consider the employee who joins on 22/07/2021 and becomes eligible for leave on 01/01/2022. The organization's leave policy offers 30 days of privilege leave at the start of the year. In this scenario, the employee will obviously be eligible for proportionate leave, which can be calculated as follows:
30 days of leave per year translates to 2.5 days of leave per month. Full months available are August, September, October, November, and December, totaling 5 months, with 10 days in July. Therefore, the calculation is as follows: 2.5 * 5 + (2.5/30) * 10 = 12.5 + 0.83 = 13.33, which rounds down to 13 days as of 01.01.2022.
S K Bandyopadhyay (WB, Howrah) CEO-USD HR Solutions +91 98310 81531 skb@usdhrs.in USD HR Solutions – To strive towards excellence with effort and integrity
From India, New Delhi
Acknowledge(0)Amend(0)
367Let us consider the employee who joins on 22/07/2021 and becomes eligible for leave on 01/01/2022. The organization's leave policy offers 30 days of privilege leave at the start of the year. In this scenario, the employee will obviously be eligible for proportionate leave, which can be calculated as follows:
30 days of leave per year translates to 2.5 days of leave per month. Full months available are August, September, October, November, and December, totaling 5 months, with 10 days in July. Therefore, the calculation is as follows: 2.5 * 5 + (2.5/30) * 10 = 12.5 + 0.83 = 13.33, which rounds down to 13 days as of 01.01.2022.
S K Bandyopadhyay (WB, Howrah) CEO-USD HR Solutions +91 98310 81531 skb@usdhrs.in USD HR Solutions – To strive towards excellence with effort and integrity
From India, New Delhi
Acknowledge(0)Amend(0)Gathering data for an AI comment.... Sending emails to relevant members...
Join Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
Leave Balance Tracker - Final.xls
CiteHR.AI
(Fact Checked)-The calculation provided in the user reply is correct based on a pro-rata calculation for leave entitlement when an employee joins mid-year. (1 Acknowledge point)