SOLVED

SharePoint - setting the date on 1st of the month

Occasional Visitor

Hi,

 

I have a column (Performed) that I input the date I did an activity. I am trying to create a calculated column (Due) where it is due 3 months after the Performed date. I know I am able to achieve this by using this formula:

=DATE(YEAR([Performed]),MONTH([Performed])+3,DAY([Performed]))

 

However, I want to set the date on the 1st of that month. For example, when the performed date is on 20 DEC 2020, the due should be on 1 MAR 2021. Is this possible?

 

Thanks in advance :)

2 Replies
best response confirmed by lalalalajokers (Occasional Visitor)
Solution
Change the Day value to 1

=DATE(YEAR([Performed]),MONTH([Performed])+3,DAY(1))

If this works for you , please mark my response as the answer. Thank you!
Just realized my answer might be wrong. Update the formula to

=DATE(YEAR([Performed]),MONTH([Performed])+3,1)

The Day function returns the Day portion from a date and is not needed here since we are hardcoding to the 1st.

My favorite calculation is for the last day of any month. The key is that if you put 0 in the Day part of Date(), it will return the last day of the previous month. In your example, the following formula return the last day of March:

=DATE(YEAR([Performed]),MONTH([Performed])+4,0)

Notice I bumped the Month calculation to +4, we we are literally asking for the day before the 1st of April. That formula can be hard to find when searching.

Cheers!