Others

How to Add One Year to a Date Using a Formula in Microsoft Lists

Phi Hung Vo Oct 14, 2026 868 views

Question details

The user needs to create a calculated column in Microsoft Lists that takes a date from another column and automatically adds exactly one year to it.

How to Add One Year to a Date Using a Formula in Microsoft Lists

Product: Microsoft Lists
Device & OS: not provided
Scenario: Setting up automated tracking for future dates, such as annual expirations, renewals, or reviews based on an initial date entry.
Observed behavior: Users require the correct formula syntax and steps to apply it, while also needing guidance on how to bypass limitations in the modern web interface where calculated columns might appear missing.

Before you start

Ensure you have Edit or Owner permissions for the Microsoft List, as these rights are required to access list settings and create calculated columns.

Solution 1Recommended

Create a Calculated Column via List Settings

The most reliable method to add a date calculation formula is by using the classic List Settings menu.

In Microsoft Lists, the DATE function allows you to rebuild a date by extracting the year, month, and day from an existing column and adding values to them.

Access List Settings

Open your Microsoft List, click on the gear icon (Settings) in the top right corner, and select "List settings" from the dropdown menu.

Create a New Column

Scroll down to the "Columns" section on the settings page and click "Create column".

Set Column Type

Enter a name for your new column (e.g., "Next Year Date") and select "Calculated (calculation based on other columns)" as the column type.

Enter the Date Formula

In the "Formula" box, enter `=DATE(YEAR([SourceColumn])+1,MONTH([SourceColumn]),DAY([SourceColumn]))`. Replace `[SourceColumn]` with the exact name of your original date column.

Configure Data Type

Under the formula box, ensure that "The data type returned from this formula is" is set to "Date and Time". Choose your preferred date format, then click "OK" to save.

Create a Calculated Column via List Settings

Handling Syntax Errors: If you receive a syntax error upon saving, your regional settings might require semicolons instead of commas. If so, use this formula instead: =DATE(YEAR([Column 1])+1; MONTH([Column 1]); DAY([Column 1]))

Solution 2

Add a Calculated Column from the Modern Web Interface

If you cannot find the gear icon or "List settings", you can try forcing the classic column creation page through the modern column headers.

Free Microsoft Office alternative

Manage Data and Track Dates Easily with WPS Office

While Microsoft Lists is a great tool for tracking, managing large data sets and complex date formulas is often much easier in a dedicated spreadsheet. WPS Spreadsheet offers full compatibility with Microsoft Excel formulas, allowing you to seamlessly calculate dates, track project timelines, and organize your lists offline or online for free.

1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
2. Import Your Data: Export your Microsoft List to CSV or Excel format, then open it directly in WPS Spreadsheet.
3. Calculate Your Dates: Select an empty column and use the standard `=EDATE(A2, 12)` or `=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))` formula to instantly add one year to your dates.

Fully compatible with Microsoft Excel (.xlsx) files and standard spreadsheet date formulas.Lightweight and fast, offering a smooth data management experience without web browser interface limitations.Includes a comprehensive set of built-in date and time functions for effortless timeline tracking.Completely free to download and use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Calculated Column option missing when I click Add Column?

In the modern web version of Microsoft Lists, the Calculated Column option is frequently omitted from the quick 'Add column' menu. You must click 'More...' at the bottom of the menu or navigate to 'List Settings' via the gear icon to access the full list of column types.

Can I modify the formula to add multiple years or months?

Yes. To add multiple years, change the '+1' in the YEAR section to your desired number (e.g., YEAR([Date])+3). To add months instead, leave the YEAR section alone and append the number to the MONTH section, such as MONTH([Date])+6.

What should I do if my date formula returns a five-digit number instead of a date?

This happens when the calculated column is formatted as a number rather than a date. Go back to edit the column settings, and explicitly change 'The data type returned from this formula is' setting from 'Number' to 'Date and Time'.

Office Quick Solutions