SharePoint Document Issues

How to Create a SharePoint Calculated Column Returning Yes, No, or NA

Adam Davis Oct 22, 2026 868 views

Question details

The user needs to generate a Yes, No, or NA result in a SharePoint training list based on complex criteria including completion date, training frequency, group membership, and end date.

Create a SharePoint Calculated Column Returning Yes, No, or NA

Product: SharePoint
Device & OS: not provided
Scenario: Managing a training compliance list in SharePoint that requires automated status tracking based on multiple conditional factors.
Observed behavior: Requires a multi-condition formula returning specific text values (Yes, No, NA) depending on date and grouping criteria.

Before you start

Ensure you have the exact internal column names for your Completion Date, End Date, Training Frequency, and Group Membership fields, as SharePoint formulas require precise name matching to function properly.

Solution 1Recommended

Build a Nested IF and AND Formula in SharePoint

Construct a calculated column using standard SharePoint formulas to evaluate the dates and group conditions.

SharePoint allows you to combine IF and AND logic directly within a calculated column to check if the completion date falls within the last 365 days, occurs before the end date, and matches the correct group.

Access list settings

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

Create a new column

Scroll down to the Columns section and click the 'Create column' link.

Enter the formula

Name your column (e.g., 'Training Status'), choose 'Calculated (calculation based on other columns)', and enter your nested formula in the syntax: =IF(AND([Completion Date]>=TODAY()-365, [Completion Date]<[End Date]), "Yes", IF([Group]="Exempt", "NA", "No")).

Define output type

Select 'Single line of text' as the data type returned from this formula, then click 'OK' to save your new calculated column.

Build a Nested IF and AND Formula in SharePoint

Formula Limitations: SharePoint calculated columns do not automatically update based on the [Today] function over time; they only recalculate when the specific list item is created or modified.

Solution 2

Integrate with Power Apps for Advanced Status Logic

Bypass SharePoint's calculated column date limitations by handling the logic dynamically within a customized Power Apps form.

Free Microsoft Office alternative

Use WPS Spreadsheet to Track Complex Training Matrices

If SharePoint's formula limitations are too restrictive for your training status tracking, consider managing complex data matrices in WPS Spreadsheet. It offers powerful, fully compatible Excel formulas (like IFS and XLOOKUP) completely for free.

1. Download WPS Office: Visit the official WPS Office website to download and install the free suite.
2. Open your data: Launch WPS Spreadsheet and either create a new tracking document or open your exported SharePoint list.
3. Apply advanced formulas: Use standard Excel-compatible formulas like =IFS() to easily and dynamically generate Yes, No, or NA results based on your training criteria.

Fully compatible with Microsoft Excel (.xlsx) formats and formula syntax.Easily handles complex nested IF statements and real-time date calculations without SharePoint's evaluation limitations.Lightweight, fast, and completely free to use for daily spreadsheet tracking and reporting.Familiar interface ensures a seamless transition for anyone used to Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my SharePoint calculated column updating with the current date?

SharePoint calculated columns only evaluate when an item is actively created or edited. Using [Today] in formulas is generally unsupported for live, ongoing updates. For real-time daily date tracking, consider setting up a scheduled Power Automate flow instead.

Can I return a blank cell instead of NA in a calculated column?

Yes, simply use empty quotes ("") as the final false condition in your IF statement. For example: =IF([Condition], "Yes", "").

What should I do if my nested IF formula exceeds SharePoint character limits?

If your formula logic becomes too complex or exceeds SharePoint's limitations, it is highly recommended to migrate the logic to a customized Power Apps form or use a Power Automate flow to calculate the result and update a standard text column.

Document & File Issues