Excel Months Left in Year: Formula & Guide

Formulas & functions · Updated November 22, 2026

To calculate the months left in the year from a date in Excel, use the formula =DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"m") — this returns the number of full months remaining. If you want to include the current month as a whole (i.e., count the month you’re in as one of the remaining months), use =12-MONTH(TODAY())+1. Both formulas work in Excel for Microsoft 365, Excel 2021, and earlier versions.

Understanding the Two Main Formulas

The two formulas answer slightly different questions. The first, using DATEDIF, gives the number of complete months between today and December 31. For example, if today is March 15, 2025, DATEDIF returns 9 (April through December). The second formula, =12-MONTH(TODAY())+1, returns 10 because it counts March as a remaining month. Which one you need depends on how you define “left in the year” — for budgeting or project planning, you might want the inclusive count; for a strict count of future full months, use DATEDIF.

How to Use the Formulas with a Specific Date

If you have a date in a cell (say A1), replace TODAY() with that cell reference. For example, with a date in A1, the inclusive formula is =12-MONTH(A1)+1. The DATEDIF version becomes =DATEDIF(A1,DATE(YEAR(A1),12,31),"m"). Both assume the date is not in the future relative to the year in that date; if you use a date from a previous year, the result will be negative or zero — Excel treats dates before the start of the year as having no months left. To avoid errors, you can wrap the formula in MAX(0, ...) to return 0 for past dates.

Why the DATEDIF Function Is Not in the Function List

Many users search for “months left in year” because they can’t find DATEDIF in Excel’s function library. That’s because Microsoft keeps it for compatibility with Lotus 1-2-3 and does not list it in the Insert Function dialog. You can still type it manually. It works in all modern Excel versions, but it’s not documented in the official help. If you prefer a documented function, use =DATEDIF anyway, or use the alternative: =MONTH(DATE(YEAR(TODAY()),12,31))-MONTH(TODAY()) which gives the same result as DATEDIF but uses only standard functions.

Common Variations: Including or Excluding the Current Month

The most common confusion is whether to count the current month. Here are the three typical scenarios:

  • Full months remaining (not counting current month): =DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"m")
  • Months including the current month: =12-MONTH(TODAY())+1
  • Months remaining as of the start of the next month (for billing cycles): =DATEDIF(EOMONTH(TODAY(),0)+1,DATE(YEAR(TODAY()),12,31),"m")+1

The third formula is useful when you want to count the next month as the first full month. For example, on March 15, it returns 10 (April to December inclusive).

How to Calculate Months Left in a Year for a List of Dates

If you have a column of dates, say in A2:A100, you can fill down the formula. For each row, use =DATEDIF(A2,DATE(YEAR(A2),12,31),"m") in B2 and copy down. To handle dates from previous years, wrap with IF: =IF(YEAR(A2). This returns 0 for any date before January 1 of the current year. For a dynamic list that updates with today's date, use TODAY() as in the first example — it recalculates whenever the workbook opens.

Using the Formula for Financial or Project Planning

These formulas are common in financial models to calculate remaining months for depreciation schedules, loan amortization, or project timelines. For example, to calculate the number of months left in a fiscal year that ends June 30, replace December 31 with the fiscal year end: =DATEDIF(TODAY(),DATE(YEAR(TODAY()),6,30),"m") — but note that if today is after June 30, this returns a negative number. To handle that, use =IF(TODAY()>DATE(YEAR(TODAY()),6,30),DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,6,30),"m"),DATEDIF(TODAY(),DATE(YEAR(TODAY()),6,30),"m")). This gives the months left in the current fiscal year, rolling over to the next if the date has passed.

Frequently Asked Questions

How do I get the months left in the year as a decimal or fraction?

If you want a decimal like 9.5 for half a month, you can calculate the days remaining and divide by 30.44 (average days per month). For example: =ROUND((DATE(YEAR(TODAY()),12,31)-TODAY())/30.44,1). This gives a rough decimal. For exact months, use DATEDIF with "m" and then add the fraction of the current month: =DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"m") + (DAY(DATE(YEAR(TODAY()),12,31))-DAY(TODAY()))/DAY(EOMONTH(TODAY(),0)) — but this is rarely needed.

Why does DATEDIF return an error for some dates?

DATEDIF returns a #NUM! error if the start date is after the end date. This happens if you use a date from a previous year with December 31 of that same year — the start is after the end. To avoid, use the IF wrapper shown above.

Can I use this formula in Google Sheets?

Yes, DATEDIF and MONTH work identically in Google Sheets. The only difference is that Google Sheets has a built-in DATEDIF in the function list, but the syntax is the same. In Excel for the web, the formula also works.

How do I calculate months left in the year from a date in the past?

If the date is in the past but in the same year, the formulas work fine. For example, if today is March 15 and A1 has January 1, =DATEDIF(A1,DATE(YEAR(A1),12,31),"m") returns 11 (February through December). If the date is from a previous year, you'll get a negative number or error; use the MAX(0,...) or IF to force 0.

Is there a simpler way without DATEDIF?

Yes, for the inclusive count, =12-MONTH(TODAY())+1 is the simplest. For the exclusive count, you can use =12-MONTH(TODAY()) — but note that on the first day of a month, this gives the same as DATEDIF (e.g., on March 1, both give 9). On other days, DATEDIF still returns the number of full months, while 12-MONTH ignores the day.

What if I want the months left in the year as of a specific date, not today?

Replace TODAY() with a cell reference or a date in quotes. For example, =DATEDIF(DATE(2025,6,15),DATE(2025,12,31),"m") returns 6 (July to December). This is useful for historical analysis.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.