0

We have a list and need to create a view based on previous calendar month.

I have created two new calculated Date and Tine columns:

  • _StartPreviousMonth = DATE(YEAR(Created),MONTH(Created)-1,1)
  • _EndPrevioousMonth = DATE(YEAR(Created),MONTH(Created),1)-1

These are working as expected.

An item created in November shows start of 11/01/2023 and end of 11/30/2023. We are now in December 2023, and I need the view to show only items in November. The view filters are _StartPreviousMonth is less than or equal to [TODAY] and _EndPrevioousMonth is greater than or equal to [TODAY]. This returns all items from December 2023. I need to be able to pull November’s items. Any suggestions?

1
  • Why _EndPrevioousMonth and not _EndPreviousMonth? 😅
    – jleture
    Dec 11, 2023 at 17:54

1 Answer 1

0

An item created in November shows start of 11/01/2023 and end of 11/30/2023

No, with the formula you used, an item created in November will have 10/01/2023 and 10/31/2023. And this is correct regarding the name of the column (previous month, not current month!) and the formula (MONTH - 1).

My suggestion: create calculated columns with current month

_StartCurrentMonth = DATE(YEAR(Created),MONTH(Created),1)
_EndCurrentMonth = DATE(YEAR(Created),MONTH(Created)+1,1)-1

And then for the view, use a filter with [TODAY]-30 but in some case, it will not work (for example: in February/March).

filter today - 30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.