Excel Formula for Next Year Calendar?

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a next-year calendar is =DATE(YEAR(TODAY())+1,1,1), which returns the first day of next year as a real date. To build a full calendar grid, combine SEQUENCE with DATE and WEEKDAY to generate all dates and align them to the correct weekday columns.

Why Use a Formula Instead of Typing Dates

Typing 365 dates manually is slow and error-prone, especially if you need the calendar to update automatically when the year changes. A formula-driven calendar recalculates itself: open the workbook in January 2026 and it shows 2026; open it in 2027 and it shows 2027. This is useful for annual planners, fiscal year trackers, or dashboards that always need “next year” without manual edits.

The core idea is that Excel stores dates as serial numbers (1 = January 1, 1900). Arithmetic on dates works because adding 1 to a serial number adds one day. So =DATE(YEAR(TODAY())+1,1,1) takes the current year, adds 1, and constructs January 1 of that year. If today is March 15, 2025, this returns January 1, 2026 (serial number 46023).

Building a Full Year Grid with SEQUENCE

Excel 365 and Excel 2021 have SEQUENCE, which generates a list of numbers. To create all 365 dates of next year in a single column, put this in cell A1:

=DATE(YEAR(TODAY())+1,1,1)+SEQUENCE(365,1,0,1)

This assumes you want dates starting at January 1. The SEQUENCE(365,1,0,1) part creates 365 rows, 1 column, starting at 0, incrementing by 1. Adding that to the start date gives each day of the year. Format the column as a date (Home > Number Format > Short Date). The result is a list from Jan 1 to Dec 31 of next year.

For a month-by-month grid, use a formula that places dates in a 7-column layout (Sun–Sat or Mon–Sun). One common approach:

  1. In cell B2, enter the first day of the month: =DATE(YEAR(TODAY())+1,1,1).
  2. In cell B3, enter =IF(B2="","",IF(MONTH(B2+1)<>MONTH(B2),"",B2+1)) and drag across and down to fill a 6-row by 7-column grid. This creates dates but leaves blanks after the month ends.
  3. To align the first day to the correct weekday, use =B2-WEEKDAY(B2)+1 to get the Sunday of that week, then add offsets.

A cleaner modern method uses SEQUENCE with WEEKDAY to generate a full month grid in one formula. For example, to create a calendar for January of next year starting on Sunday in cells B2:H8:

=LET(start,DATE(YEAR(TODAY())+1,1,1), offset,WEEKDAY(start,1)-1, seq,SEQUENCE(6,7,1-offset,1), IF(MONTH(start+seq-1)=MONTH(start), start+seq-1, ""))

This returns a 6-row by 7-column array. The WEEKDAY(start,1) returns 1 for Sunday, 2 for Monday, etc. Subtracting 1 gives the number of blank cells before the first day. The IF hides dates that spill into the previous or next month. This formula works in Excel 365 and Excel 2024; in older versions you would need a helper grid with individual formulas.

How to Make It Update Automatically

The formulas above use TODAY(), which is a volatile function—it recalculates whenever the workbook recalculates or when you open the file. That means the calendar always points to the current “next year.” If you want a static year (e.g., always 2026), replace YEAR(TODAY())+1 with a cell reference like YEAR(A1)+1, where A1 contains the base year. Or simply hardcode the year: =DATE(2026,1,1).

To avoid confusion, label the year cell clearly. For example, put “Base Year” in A1 and the year number in B1, then use =DATE(B1+1,1,1) for the start date. This makes the calendar predictable and easy to change.

Common Problems and Fixes

  • Dates show as numbers like 46023. Select the cells and press Ctrl+Shift+3 (or Home > Number Format > Short Date). On Mac, use Cmd+Shift+3.
  • Blank cells show as 0 or January 0. This happens when your formula adds an offset that goes before Jan 1. Use the IF(MONTH(...)=MONTH(start), ...) check to return an empty string.
  • The grid doesn’t align to Monday-start calendars. Change WEEKDAY(start,1) to WEEKDAY(start,2) (Monday=1) and adjust the offset accordingly.
  • Leap years. SEQUENCE(365) misses Feb 29 in a leap year. Use =DATE(YEAR(TODAY())+1,1,1)+SEQUENCE(DATE(YEAR(TODAY())+2,1,1)-DATE(YEAR(TODAY())+1,1,1),1,0,1) to generate the exact number of days in that year.
  • Excel for the web or Mac. SEQUENCE and LET are supported in Excel for the web and Excel 365 for Mac. In older Excel 2019 or earlier, you must use the drag-fill method with IF formulas.

Alternatives: PivotTables and Power Query

If you need a calendar for a data model or a dynamic report, consider Power Query to generate a date table. Go to Data > Get Data > From Other Sources > Blank Query, then use the M formula = List.Dates(#date(2026,1,1), 365, #duration(1,0,0,0)) to create a list of dates. Load it as a table and you have a static date dimension. This is more robust for large datasets but requires a few clicks and is not a single-cell formula.

Another alternative is a PivotTable with a date field grouped by month and year—Excel automatically creates a calendar-like layout, but it doesn’t show blank days for alignment. For a visual calendar, a PivotTable is less flexible than a formula grid.

Comparing Formula Methods

The SEQUENCE method is the most concise and dynamic, but it requires Excel 365 or 2021. The older drag-fill method works in every Excel version but is more tedious and harder to maintain. The LET version is compact but only in Microsoft 365 and Excel 2024. If you share the workbook with users on older versions, stick to the drag-fill approach or use a static date list.

For a single cell that just shows the year, use =YEAR(TODAY())+1 formatted as a number. For a full calendar, the grid formula is best. There is no built-in “calendar” function in Excel, so these workarounds are standard practice.

Safety and Accuracy Notes

Formulas that use TODAY() are volatile—they recalculate every time the workbook opens, which can slow down very large files. If performance matters, consider using a static year cell instead. Also, always double-check that your date format is set to a recognizable date format; otherwise, you might misread serial numbers. When copying formulas, use absolute references (like $B$2) for the start date to avoid shifting.

For financial or deadline calendars, verify the year boundary: if you run the workbook on December 31, 2025, YEAR(TODAY())+1 correctly gives 2026. But if you run it on January 1, 2026, it gives 2027—so decide whether you want “next year” relative to the current date or a fixed calendar year.

Frequently Asked Questions

How do I make a calendar for a specific year, not next year? Replace YEAR(TODAY())+1 with a cell reference, e.g., =DATE(A1,1,1) where A1 contains the year number. For example, if A1 is 2027, the formula returns January 1, 2027.

Can I use this in Google Sheets? Yes, but the syntax differs slightly. Google Sheets uses SEQUENCE as well, but LET is not available (as of 2025). Use a simpler formula: =DATE(YEAR(TODAY())+1,1,1)+SEQUENCE(365,1,0,1) works in Sheets too. For a grid, you may need to use ARRAYFORMULA.

Why does my formula return a number instead of a date? The cell is formatted as General. Right-click the cell, choose Format Cells, select Date, and pick a format. Or press Ctrl+1 (Cmd+1 on Mac) to open Format Cells.

How do I highlight weekends in the calendar? Use conditional formatting. Select the grid, go to Home > Conditional Formatting > New Rule > Use a formula, and enter =WEEKDAY(B2,2)>5 (adjust the cell reference to the top-left cell of your grid). Then set a fill color.

Does this work in Excel 2019? No, SEQUENCE and LET are not available in Excel 2019. Use the drag-fill method with IF formulas, or create a static list of dates and format them.

Can I print this calendar? Yes, after generating the grid, set the print area to the grid, adjust column widths, and use Page Layout > Print Titles to repeat the header row. Ensure the page orientation is landscape for a wide calendar.

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.