SOLVED

Sp Calculated Dates with a twist

Copper Contributor

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

@DJKoala323 

 

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])))

 

 

kalpeshvaghela_0-1682490691335.png


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

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

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

@DJKoala323 

 

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])))

 

 

kalpeshvaghela_0-1682490691335.png


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

View solution in original post