Calculation Issues

Calculate Leave Accrual from a Joining Date in WPS Spreadsheet

John WilsonJohn Wilson Nov 12, 2026 868 views

Question details

Calculate the amount of leave earned from an employee’s joining date through a selected date or today.

Calculate Leave Accrual from a Joining Date in WPS Spreadsheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the joining date

In WPS Spreadsheet, select cell G4 and enter the employee’s joining date, such as 1/1/2025.

2
Enter the calculation date

Select H4 and enter the date through which leave should be calculated, or use =TODAY() in H4 to use the current date.

3
Enter the accrual formula

Select the result cell and enter =DATEDIF(G4,H4,"m")*2.5+DAY(H4)*2.5/30, then press Enter.

4
Calculate through today directly

To avoid using H4, enter =DATEDIF(G4,TODAY(),"m")*2.5+DAY(TODAY())*2.5/30 in the result cell.

5
Format the result

Select the result cell, open the Home tab, choose the Number Format control, and set the value to Number with the required decimal places.

Formula assumption: This calculation assumes that G4 is the first day of the accrual period. If the joining date can fall later in a month, the organization’s partial-month policy may require a different proration formula.

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. 1. Open WPS Spreadsheet: Launch WPS Office and open a blank spreadsheet or the employee leave workbook.
  2. 2. Add the dates: Place the joining date in G4 and the calculation date or =TODAY() in H4.
  3. 3. Calculate accrual: Enter =DATEDIF(G4,H4,"m")*2.5+DAY(H4)*2.5/30 in the result cell and press Enter.
  4. 4. Format and reuse: Apply Number formatting to the result, then copy the formula down for additional employees while updating the cell references.
Calculate completed months and 30-day prorated leave in one formulaUse TODAY() to keep the leave balance current automaticallyWork with familiar spreadsheet functions and an easy-to-use interfaceOpen and save Microsoft Excel XLS and XLSX files with compatible formatting
microsoft office alternative - wps office

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.