Zoho Sheet MONTHS Function: Complete Months or Calendar Boundaries
Compare MONTHS modes with complete date datasets, reproducible boundary examples and explicit scope for unresolved date behavior.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
MONTHS counts months between a start date and an end date using one of two methods. Mode 0 counts completed months; mode 1 compares the calendar months containing the endpoints. A boundary crossed is not always a completed month, so choosing the mode is part of specifying the question.
Availability and verification scope
Zoho’s official MONTHS entry was retrieved on October 15, 2026. It documents the rolling web service without establishing an introduction release or a last functioning version; both historical endpoints remain unknown. Current web documentation does not certify mobile or offline-client parity.
The calendar results here were independently calculated from the stated dates. They are reference expectations, not captured native Zoho output. Formulas follow the help’s semicolon notation; check the actual editor’s locale and syntax before pasting them.
Syntax and the default mode
MONTHS(start_date; end_date; [mode])
Start_date and end_date are required. Mode defaults to 0. For mode 0, Zoho describes day-of-month to day-of-month counting. Mode 1 returns the difference between the months containing the two dates.
The examples make both methods explicit in separate columns. They use forward intervals and ordinary date values. Reversed dates, timestamps, invalid mode values and month-end normalization are not fully specified by the checked entry and are not silently borrowed from another product.
Build unambiguous input dates
Use Zoho DATE with numeric year, month and day components, rather than pasting ambiguous slash-formatted text. For example, enter =DATE(2024;1;15) for January 15, 2024. All dates in the next table are displayed in year-month-day form solely to identify the intended calendar values.
The function returns a count, so format result columns as numbers, not dates. A number such as 3 displayed using a date format can look like a date near the workbook’s epoch and conceal the actual result.
Complete comparison worksheet
Create IntervalModes with Start_date in A1, End_date in B1, Whole_months in C1 and Calendar_difference in D1. Enter the dates below using DATE formulas. Fill C2 =MONTHS(A2;B2;0) and D2 =MONTHS(A2;B2;1) through row 8.
| Row | A: start | B: end | C: mode 0 expectation | D: mode 1 expectation |
|---|---|---|---|---|
| 2 | 2024-01-15 | 2024-02-14 | 0 | 1 |
| 3 | 2024-01-15 | 2024-02-15 | 1 | 1 |
| 4 | 2024-01-15 | 2024-04-14 | 2 | 3 |
| 5 | 2024-01-15 | 2024-04-15 | 3 | 3 |
| 6 | 2024-01-15 | 2025-01-14 | 11 | 12 |
| 7 | 2024-01-15 | 2025-01-15 | 12 | 12 |
| 8 | 2023-12-31 | 2024-01-01 | 0 | 1 |
The two modes differ when a calendar boundary has been crossed but the corresponding day has not yet been reached. Compare rows 4 and 5: moving the end from April 14 to April 15 completes the third month. The mode-1 result does not change in that one-day comparison.
In a separate E2 enter =MONTHS(A2;B2) without a mode. Its expected result equals C2 because the documented default is zero. Supplying 1 explicitly asks the calendar-boundary question instead.
Reproduce source controls with explicit components
Zoho prints MONTHS("2/5/2020";"7/8/2020") as 5. Under the source’s month/day reading, unambiguous equivalent inputs are February 5 and July 8, 2020: =MONTHS(DATE(2020;2;5);DATE(2020;7;8);0) independently gives 5.
Its January 1, 2018 to January 1, 2019 example independently gives 12. The entry’s mode-1 example refers to hidden E2 and F2 cells, so its displayed 2 is not used here without inventing those inputs. The complete worksheet above supplies its own visible mode-1 controls.
Advanced: distinguish completed service from calendar reporting
Suppose a fictional equipment rule checks for six complete months after January 15, 2024. Put that date in G2 and July 14, 2024 in H2. =MONTHS(G2;H2;0) independently gives 5, while mode 1 gives 6. The completed-month comparison =MONTHS(G2;H2;0)>=6 is FALSE.
Changing H2 to July 15 makes both counts 6 and the same comparison TRUE. This is an explicit arithmetic policy example, not a rule for contracts, wages or legal eligibility. The required business definition should decide which count is appropriate.
Advanced: audit calendar-month distance
For mode 1, an independent calendar index is 12year+month. The difference from November 2023 to February 2024 is (122024+2)−(12*2023+11)=3. Dates November 20, 2023 and February 5, 2024 therefore have a mode-1 expectation of 3, but only 2 complete day-to-day months.
Do not divide elapsed days by 30 to reproduce whole calendar months. January and February have different lengths, and the day-to-day completion rule is not a fixed-length duration conversion. Keep exact elapsed days as a separately named measure if the task needs them.
Errors and troubleshooting
The entry lists shared name, type and reference errors rather than a detailed MONTHS-specific domain table. An invalid mode, reverse interval or blank input should not be assigned a guessed error code. The worked controls avoid those unresolved cases.
If the count differs by one, compare the endpoint days of the month and the selected mode. If it differs by a large amount, verify the dates were parsed into the intended years. If the result resembles a date, change its number format rather than changing the formula.
The examples demonstrate the documented methods on fully specified dates. They do not certify every end-of-month, leap-day, historical-calendar or native coercion rule. Keep those boundaries explicit when transferring a workbook between spreadsheet products.