SOLVED

# Sp Calculated Dates with a twist

Copper Contributor

# Sp Calculated Dates with a twist

I am trying to create a calculated date column that does the following:
1) Adds one month to a column that contains a date
2) Resets the day portion of the date to a specific number (like the 6th of the month).

Here's an example:
Entered date: 4/15/2023 (MM/DD/YYYY)
Desired day of the month: 6
Expected result: 5/6/2023

Calculated result: 5/4/2023

This is the formula I am using:
=IF(Frequency="monthly",(DATE(YEAR([Last deliverable date]),MONTH([Last deliverable date])+[Date Calculation Input],DAY([Due Day of the month]))))

Data in the fields:
Frequency: monthly

Last Deliverable Date: 4/15/2023
Date Calculation Input: 1 (number field)
Due Day of the month: 6 (number field)

Thanks for any help.

2 Replies
best response confirmed by DJKoala323 (Copper Contributor)
Solution

# Re: Sp Calculated Dates with a twist

Try without Day function for day, it should work:

=IF(Frequency="monthly",(DATE(YEAR([Last deliverable date]),MONTH([Last deliverable date])+[Date Calculation Input],[Due Day of the month])))

Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community

# Re: Sp Calculated Dates with a twist

That works--thank you for your help.
1 best response

Accepted Solutions
best response confirmed by DJKoala323 (Copper Contributor)
Solution

# Re: Sp Calculated Dates with a twist

Try without Day function for day, it should work:

=IF(Frequency="monthly",(DATE(YEAR([Last deliverable date]),MONTH([Last deliverable date])+[Date Calculation Input],[Due Day of the month])))

Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community