How to Use IF Formula to Add Days to a Date in Microsoft Lists
Question details
The user wants to conditionally add a specific number of days to a base date in Microsoft Lists based on the value of a Yes/No column.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Creating a calculated column that adjusts a target date differently if a specific condition (Yes/No) is met.
- Observed behavior
- The user needs the correct formula structure to output the calculated date, and is looking for ways to handle complex scenarios like skipping weekends and holidays.
Ensure you have list editing permissions to create calculated columns, and verify the exact names of your base date column and Yes/No column.
Use an IF Formula in a Calculated Column
Use a standard IF statement to evaluate the Yes/No column and mathematically add the appropriate number of days to your base date.
Navigate to your Microsoft List, click on 'Add column' at the end of your headers, and select 'Calculated' from the available column types.
Name your new column (e.g., 'Adjusted Due Date') and select 'Date and Time' under the section asking for the data type returned from this formula.
In the formula box, type the conditional logic: `=IF([Yes/No Column]=TRUE,[Date Base]+1,[Date Base]+4)`. Ensure you replace the bracketed text with the exact internal names of your list columns.
Click 'OK' or 'Save' at the bottom of the settings pane. Your list will now automatically compute and display the updated date based on the Yes/No toggle.
Calculate Working Days Using Excel and Power Automate
Since Microsoft Lists cannot easily calculate working days natively, use Excel's WORKDAY function and sync the results via Power Automate.
Manage and Calculate Complex Dates Easily in WPS Office
While Microsoft Lists limits your ability to calculate working days natively, dedicated spreadsheet tools are perfect for this. WPS Office provides a powerful, free alternative to Microsoft Excel with complete support for advanced date calculations, allowing you to manage project timelines without complex setups or Power Automate flows.
Frequently Asked Questions
Why does my IF formula return a syntax error in Microsoft Lists?
Syntax errors usually occur if the column names in your formula do not exactly match the internal names in the list, or if you are missing square brackets around column names containing spaces (e.g., `[Start Date]`). Also, ensure you are using the correct list separator (comma or semicolon) based on your regional settings.
Can I format the output of the calculated date column to show only the date?
Yes. When creating or editing the calculated column, look for the 'The data type returned from this formula is' setting. Select 'Date and Time', and then choose 'Date Only' rather than 'Date & Time' for the display format.
Does Microsoft Lists have a WORKDAY function to automatically exclude weekends?
No, Microsoft Lists and SharePoint calculated columns do not natively support the WORKDAY function. To skip weekends, you must either use highly complex nested IF statements to account for the weekday of the base date, or perform the calculation in an external spreadsheet app and sync it using Power Automate.
