Others

SharePoint Calculated Column Formula for Expiry Dates

Phi Hung Vo Sep 27, 2026 869 views

Question details

The user needs a SharePoint calculated column formula to determine an expiry date by adding a specific number of years from a 'Validity' choice column to a 'Decision Date'.

How to Calculate an Expiry Date in a SharePoint List

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Automating expiry date calculations dynamically based on user inputs in a SharePoint list, replicating logic previously handled in Microsoft Access.
Observed behavior: The standard addition calculation fails because the list uses a Choice column type for the validity years, which SharePoint does not automatically treat as a numeric value for date math.

Before you start

Ensure you have 'Design' or 'Full Control' permissions for the SharePoint list to add or modify calculated column settings.

Solution 1Recommended

Use the VALUE Function for Choice Columns

This method is best when you must keep your 'Validity' field as a Choice column and want a straightforward calculation based on multiplying years by 365 days.

SharePoint often treats Choice columns as text strings rather than numbers. To perform mathematical operations like adding days to a specific date, you must convert the choice string into a numeric value using the VALUE function.

Access List Settings

Navigate to your SharePoint list, click the Gear icon in the top right corner, and select 'List settings'.

Create a Calculated Column

Scroll down to the Columns section and click 'Create column'. Name the new column 'Expiry Date' and select 'Calculated (calculation based on other columns)' as the type.

Enter the IF and VALUE Formula

In the formula box, type: =IF(Validity="0","",[Decision Date]+VALUE(Validity*365)). This formula leaves the field blank if validity is 0, otherwise, it converts the choice to a number and adds the equivalent days.

Set the Output Type

Under 'The data type returned from this formula is:', select 'Date and Time', choose 'Date Only', and click 'OK' to save.

Leap Year Limitations: Multiplying by 365 does not natively account for leap years, meaning the calculated expiry date might be off by a day or two over longer validity periods.

Solution 2

Use Exact Date Functions (Recommended for Calendar Accuracy)

Use this precise formula structure to account for leap years perfectly by breaking down the date into its exact YEAR, MONTH, and DAY components.

Free Microsoft Office alternative

Manage Data and Date Formulas with WPS Office

If you are managing lists locally or exporting SharePoint data for deeper analysis, WPS Spreadsheet provides a free, lightweight, and highly compatible alternative to Microsoft Excel for all your tracking needs.

1. Export your SharePoint List: Use the 'Export to Excel' feature in SharePoint to download your list data as an offline file.
2. Open in WPS Spreadsheet: Launch WPS Office and open the exported file. The interface perfectly matches what you expect from traditional spreadsheet software.
3. Apply Advanced Formulas: Easily use standard Excel-compatible IF, DATE, and VALUE formulas directly within WPS to manage your expiry trackers.

100% compatible with Microsoft Excel (.xlsx) files and standard DATE/VALUE formulasFree, lightweight suite that loads quickly on both Windows and Mac devicesFamiliar user interface for a seamless migration from other Office programsAdvanced data management, formatting, and offline capabilities without subscription fees

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SharePoint calculated column display a #VALUE! error?

This error occurs when SharePoint attempts to perform mathematics on a text string. If you are using a Choice column for numbers, you must wrap the column reference in the VALUE() function, such as VALUE([Validity]).

How do I leave a calculated date field blank if the source date is missing?

You can use an IF and ISBLANK statement to check for empty values before calculating. For example: =IF(ISBLANK([Decision Date]),"", [Decision Date]+365).

Can I format the Expiry Date output as text instead of a Date?

Yes, you can use the TEXT function to format the result directly in the formula. For example: =TEXT([Decision Date]+365, "MM/DD/YYYY"). Note that if you do this, you must set the column output data type to 'Single line of text'.

Office Quick Solutions