How to Set a Due Date on the First Day of the Month in SharePoint
Question details
The user wants to calculate a future deadline by adding a specific number of months to a base date and ensuring the resulting date falls on the exact first day of that future month.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating a calculated column in a SharePoint list to automatically determine and track project deadlines.
- Observed behavior
- The user requires a formula that accurately processes a base date, advances it by three months, and forces the day output to the first day of the resulting month.
Ensure you have the necessary permissions to edit the SharePoint list and create calculated columns. Verify the exact name of your base date column (e.g., 'Performed'), as formula references in SharePoint are case-sensitive.
Use the DATE Function in a Calculated Column
Apply the DATE formula to extract the year and month from your original date, add the required number of months, and set the day parameter to 1.
SharePoint handles dates similarly to Microsoft Excel. By utilizing the DATE, YEAR, and MONTH functions together, you can easily manipulate a starting date to output any future date.
In this solution, we will add three months to the original date and force the day parameter to output as the first day of that month.
Navigate to your SharePoint list, click on the settings gear icon in the top right corner, and select 'List settings'.
Scroll down to the Columns section and click the 'Create column' link.
Enter a name for your new column (such as 'Due Date') and select 'Calculated (calculation based on other columns)' as the column type.
In the Formula box, enter the following code: =DATE(YEAR([Performed]),MONTH([Performed])+3,1) . Be sure to replace '[Performed]' with the actual name of your starting date column.
Set the data type returned from this formula to 'Date and Time', choose the 'Date Only' format, and click 'OK' to apply your changes.
Manage Data and Date Calculations with WPS Office
While SharePoint handles list formulas online, you can perform the exact same advanced date calculations and dataset management seamlessly using WPS Spreadsheet. It is a free, lightweight alternative to Microsoft Office that offers powerful calculation capabilities.
Frequently Asked Questions
Can I subtract months instead of adding them using this formula?
Yes, you can subtract months by changing the plus sign to a minus sign in the MONTH segment of the formula. For example: =DATE(YEAR([Performed]),MONTH([Performed])-3,1) will return a date three months in the past.
What if my base date column has spaces in its name?
If your column name contains spaces, you must enclose it in square brackets within the formula. For example, if the column is named 'Start Date', your reference should be written as [Start Date].
Why am I getting a syntax error when saving the calculated column?
Syntax errors in SharePoint calculated columns often occur due to regional settings. If your SharePoint site is set to a region that uses semicolons instead of commas to separate formula arguments, change the commas in the formula to semicolons: =DATE(YEAR([Performed]);MONTH([Performed])+3;1).
Will this formula account for the changing years automatically?
Yes, the DATE function intelligently handles month overflows. If adding months pushes the calculation past December, the function will automatically increment the year without requiring additional complex logic.
