SOLVED

SharePoint - setting the date on 1st of the month

Copper Contributor

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

3 Replies
best response confirmed by lalalalajokers (Copper Contributor)
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!

@Don Kirkham 

 

Hi there!

Thank you Don for that answer! Fantastic info!
I'm wondering if you have any suggestions on an easy-ish way to create a view in a document library for what I need.
Basically... view that will filter from the 11th of last month through the 10th of the current month.

For example - today is 12/28, so we are in the December invoice cycle, and I need to see 12/11 - 1/10. If today was December 6, we would be in the November invoice cycle and need to see 11/11 - 12/10.

Thanks in advance if you're able to help!!

1 best response

Accepted Solutions
best response confirmed by lalalalajokers (Copper Contributor)
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!

View solution in original post