How to Automatically Calculate Employee Leave from DOJ in Excel? Need Help!

Hi, if I provide the Date of Joining (DOJ) and the applicable leave for that particular employee, it should calculate automatically. For example, if the DOJ is 15-Jul-2013, I need to calculate the applicable leave from this date to December. We have decided to assign 1.5 days of leave per month. Can anyone help me calculate this in Excel?

I have attached an Excel file as an example. I did the calculations manually, but I want to use a formula that will populate the applicable column.

Regards.

Please provide an explanation as to why you add 1 in this formula: =6*1.5+1. Alternatively, you can use the formula =COUNT(I6:N6)*1.5 after creating an attendance tracker. Once the tracker is set up, you can calculate this formula. You may also refer to the attached file for further information.

Thank you.

Dear Gayathri, Just to help you, first of all, if a person has joined on 15th July 2013, he will complete 6 months on 15th Jan '14. Until then, the accumulated leave as per your instruction is that in one month, the leave will be 1.5, totaling 9 days.

Date of Joining Discrepancy

In your chart, you have taken 22nd July as the date of joining. Why is that? It should be the 15th as mentioned by you. Then, you have to carry forward the leave every month after deducting the leaves taken each month. Finally, as shown in the chart, he will be left with only one day of leave. Also, you are only considering paid leave; sick and casual leave are fixed as per the company policy.

Leave Calculation Details

For paid leave, for every 20 days worked, you earn 1 leave. In the chart, you will need to account for all types of leaves, including LWP (Leave Without Pay) and any other leave policies that your company follows. I will try to attach your file back with some modifications.

Regards

Thank you for your help. I posted the sample data. I can't reveal the original data. I'm attaching a sheet without employees' names and employee numbers. I have found the formula. Please correct me if I'm wrong.

Regards.

According to our policy, yearly leave is 20 days. I want to divide it into every month (1.67 days). If the employee does not take leave in a particular month, I would like to carry forward that leave to the next month. I need to do this for every month in a year based on their joining date. Finally, I want to calculate the total leave taken.

I hope I have explained it correctly. Please assist me with this.

Thank you.