How to Convert Days into Months and Days Using 30-Day Months in WPS Office Spreadsheets
Question details
The user needs an Excel-compatible formula that converts a total number of days into months and remaining days using fixed 30-day months instead of calendar months.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has start and end dates in a spreadsheet and needs to show the difference as months and days based on 30-day months, for example 76 days as 2 months and 16 days.
- Observed behavior
- DATEDIF returns calendar-based months and days, so its results may not match a fixed 30-day-month convention such as 76 days equal to 2 months and 16 days.
Open your workbook in WPS Office Spreadsheets and confirm that the start date in F3 and the end date in H3 are real date values, not text. If your dates are in different cells, adjust the cell references in the formulas below to match your sheet.
Convert Total Days to 30-Day Months and Remaining Days with INT and MOD
Use this formula when you need a fixed 30-day-month result from a date difference, such as 76 days becoming 2 months and 16 days.
Excel's DATEDIF function returns calendar months and days, so it does not follow the 30-day-month convention used in many billing or project calculations. Converting the total number of days with INT and MOD gives a consistent 30-day-month result.
Click an empty cell such as J3 and type =DATEDIF(F3,H3,"d") or =H3-F3 to calculate the total number of days between the start date in F3 and the end date in H3. Press Enter.
Click another empty cell, for example K3, where the converted months and days should appear.
Type =INT(J3/30)&" month(s), "&MOD(J3,30)&" day(s)" and press Enter. This divides the total days by 30 and shows the remainder as days.
If your data has more rows, select K3 and drag the fill handle from the bottom-right corner down through the remaining rows.
Count Both the Start and End Dates When the Duration Must Be Inclusive
Use this adjustment when you need to include both the start date and end date in the day count before converting to 30-day months.
Convert Days to 30-Day Months and Days in WPS Office Spreadsheets
WPS Office Spreadsheets can directly perform this date conversion using the same Excel-compatible DATEDIF, INT, and MOD formulas. Enter your date difference, divide by 30, and display the remaining days without any add-ins.
- 1. Download and install WPS Office: Go to the official WPS website and download WPS Office, then run the installer and follow the on-screen steps.
- 2. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the file that contains your start and end dates.
- 3. Enter the total-days formula: Click an empty cell, type =DATEDIF(F3,H3,"d") or =H3-F3, and press Enter.
- 4. Enter the month/day conversion formula: In another cell, type =INT(J3/30)&" month(s), "&MOD(J3,30)&" day(s)" and press Enter to see the fixed 30-day-month result.
Frequently Asked Questions
Why does DATEDIF not return 30-day months?
DATEDIF works with calendar months and days between two dates, so it follows actual calendar lengths rather than a fixed 30-day month. To get a fixed result, calculate total days first and then use INT and MOD.
What formula converts 76 days into months and days?
If 76 is in J3, use =INT(J3/30)&" month(s), "&MOD(J3,30)&" day(s)". It returns '2 month(s), 16 day(s)'.
How do I include both start and end dates in the count?
Add 1 to the total days: =DATEDIF(F3,H3,"d")+1 or =H3-F3+1, then use the same INT/MOD conversion formula so it uses the updated total.
Why does my formula return #VALUE!?
Check that the dates are true date values and that the total-days cell contains a number. If it contains text such as '30 days', INT and MOD cannot calculate it.
Can I use these formulas in WPS Office Spreadsheets?
Yes. WPS Spreadsheets supports Excel-compatible formulas including DATEDIF, INT, and MOD, and can open .xlsx workbooks.
