Forum Discussion
Solved
SharePoint - setting the date on 1st of the month
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 ach...
- 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!
=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!
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!!