Calculate Leave Accrual from a Joining Date in WPS Spreadsheet
Question details
Calculate the amount of leave earned from an employee’s joining date through a selected date or today.

- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The employee earns 2.5 leave days per month, and any partial month must be prorated using a 30-day month.
- Observed behavior
- The user needs a spreadsheet formula that returns completed monthly accrual plus prorated leave for the remaining days.
Enter the joining date in G4 and the calculation date in H4, and verify that both cells use a date format. This method assumes the joining date is the first day of the accrual period and uses 2.5 as the monthly accrual rate.
Use DATEDIF to calculate completed and prorated leave
Use this formula when the joining date is in G4, the calculation date is in H4, and partial months are prorated over 30 days.
The formula calculates completed months with DATEDIF, multiplies them by 2.5 leave days, and adds the prorated value for the calculation date’s day number. The result is a numeric leave balance that can be displayed with the required decimal places.
In WPS Spreadsheet, select cell G4 and enter the employee’s joining date, such as 1/1/2025.
Select H4 and enter the date through which leave should be calculated, or use =TODAY() in H4 to use the current date.
Select the result cell and enter =DATEDIF(G4,H4,"m")*2.5+DAY(H4)*2.5/30, then press Enter.
To avoid using H4, enter =DATEDIF(G4,TODAY(),"m")*2.5+DAY(TODAY())*2.5/30 in the result cell.
Select the result cell, open the Home tab, choose the Number Format control, and set the value to Number with the required decimal places.
Calculate Employee Leave Accrual Easily in WPS Spreadsheet
WPS Spreadsheet lets you enter joining and calculation dates, use DATEDIF and TODAY in a reusable formula, and format the resulting leave balance with the required decimal precision.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a blank spreadsheet or the employee leave workbook.
- 2. Add the dates: Place the joining date in G4 and the calculation date or =TODAY() in H4.
- 3. Calculate accrual: Enter =DATEDIF(G4,H4,"m")*2.5+DAY(H4)*2.5/30 in the result cell and press Enter.
- 4. Format and reuse: Apply Number formatting to the result, then copy the formula down for additional employees while updating the cell references.

Frequently Asked Questions
How do I calculate leave up to the current date?
Enter =DATEDIF(G4,TODAY(),"m")*2.5+DAY(TODAY())*2.5/30 in the result cell. TODAY() updates the calculation automatically whenever the spreadsheet recalculates.
What does the 2.5 in the formula represent?
The 2.5 represents the employee’s leave accrual rate of 2.5 days for each completed month. Replace it with the applicable monthly rate if the policy changes.
Why does the formula divide by 30?
The division by 30 prorates the monthly accrual across a standardized 30-day month, so each remaining day earns 2.5/30 leave days.
Can I use a date in H4 instead of TODAY()?
Yes. Enter a specific calculation date in H4 and use =DATEDIF(G4,H4,"m")*2.5+DAY(H4)*2.5/30. This is useful for calculating leave as of a past or future date.
What if the joining date is not the first day of the month?
The stated formula assumes that G4 is the first day of the accrual period. If employees join on different days, apply your organization’s exact partial-month rule and adjust the proration formula accordingly.




