How to Calculate Years, Months, and Days Using a 365-Day Year and 30-Day Month in WPS Spreadsheets
Question details
The user needs a reproducible way to calculate an interval as years, months, and days using a fixed 365-day year and 30-day month convention, and to understand why example results may not match.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has start and end dates and wants to decompose the interval into fixed-length years of 365 days and months of 30 days.
- Observed behavior
- The supplied examples do not consistently match a strict 365-day or 360-day decomposition, so the arithmetic convention must be defined before choosing a formula.
Confirm the exact day-count convention before writing formulas: fixed 365-day years and 30-day months are arithmetic rules, not calendar-date math. Also decide whether the start date counts as day 1 or as zero.
Use QUOTIENT and MOD to Calculate Fixed 365-Day Years and 30-Day Months
Best when the elapsed actual days are known and you need a transparent arithmetic decomposition in WPS Spreadsheets.
A fixed 365-day year and 30-day month is a custom arithmetic convention, not a calendar-date calculation. The formulas first measure the total elapsed actual days from a baseline subtraction, then divide that total by 365 for years, by 30 for months, and return the remaining days.
Open WPS Spreadsheets, enter the start date in cell A2 and the end date in cell B2.
In cell C2, type =B2-A2 and press Enter to get the total number of calendar days between the two dates.
In cell D2, type =QUOTIENT(C2,365) and press Enter to return full 365-day years.
In cell E2, type =QUOTIENT(MOD(C2,365),30) and press Enter to return full 30-day months from the remainder.
In cell F2, type =MOD(C2,30) and press Enter to return the remaining days.
Use the 360-Day Year Convention When the Dates Follow a 30/360 Rule
Use this approach if the intended calculation follows a financial 30/360 day-count instead of a strict 365-day year.
Calculate Custom Year and Month Differences in WPS Spreadsheets
Use WPS Spreadsheets to calculate elapsed days and decompose them into fixed 365-day years and 30-day months with simple QUOTIENT and MOD formulas, keeping your date calculations transparent and editable.
- 1. Enter your start and end dates: Open WPS Spreadsheets and type the start date in one cell and the end date in another, for example A2 and B2.
- 2. Calculate elapsed actual days: In a helper cell, enter =B2-A2 to get the number of calendar days between the two dates.
- 3. Decompose into fixed years: Use =QUOTIENT(B2-A2,365) to return the number of complete 365-day years in the elapsed day count.
- 4. Decompose remaining months and days: Use =QUOTIENT(MOD(B2-A2,365),30) for months and =MOD(B2-A2,30) for remaining days.
- 5. Adjust for inclusive or 360-day conventions: If your convention counts the start date as day 1, add 1 to B2-A2; if you need a 360-day year, replace 365 with 360.
Frequently Asked Questions
Why don't my example dates match a 365-day year and 30-day month formula?
Fixed 365-day years and 30-day months are arithmetic conventions, not calendar-date calculations. Many mismatches come from counting the start date as day 1 instead of day 0, or from using elapsed actual days when the example expects inclusive dates.
How do I calculate the difference using a strict 360-day year in WPS Spreadsheets?
Calculate elapsed days first with =B2-A2. Then use =QUOTIENT(C2,360) for years, =QUOTIENT(MOD(C2,360),30) for months, and =MOD(C2,30) for days.
Can I use DATEDIF for a 365-day year and 30-day month calculation?
No. DATEDIF uses actual calendar months and years, not fixed 365-day years or 30-day months. Use QUOTIENT and MOD formulas instead.
How do I include the start date in the calculation?
Add 1 to the elapsed day count. For example, use =B2-A2+1 instead of =B2-A2 before applying the QUOTIENT and MOD formulas.
What is the difference between calendar date difference and fixed 365-day/30-day arithmetic?
Calendar date difference uses the actual varying lengths of months and leap years. Fixed 365-day/30-day arithmetic treats every year as 365 days and every month as 30 days, so it is simpler but not calendar-accurate.
