Forum Discussion
List View to display items for the next month
Create a calculated column Reminder Date and set it to [Due Date] - 30. Don't forget to set the data type to Date and Time.
Then create a view where Reminder Date is greater [Today].
Thanks for your reply. Your suggestion is very close to what I am after. It a couple of limitations though;
1. for items that are due on the 31/07/2022 as 01/07/2022.
2. If I change it to [Today]-31 I get a similar issue where 01/07/2022 goes to 31/05/2022.
3. 1st-3rd/03/2022 would be 28th-30th/01/2022.
Thanks again 🙂
You can keep the year, decrement the month, keep the day.
=DATE(YEAR(End),(MONTH(End)-1),DAY(End))If the date created isn't valid, it handles it by adding the number of days that are missing. So it the date falls on the 31st, it won't be a valid date in the previous month. It is off by 1 day and adds a day. For a March 31 due date, in most years, that is off by 3 days (4 in leap year) - and those days will be added.
Power Automate is also a good option depending on how much logic you need to add.
Thank you for the post. It has enabled me to find the solution I was after. I have detailed below what I came up with;
Created 4 calculated fields with the following formulas;
- (Due Date-Digit)
=TEXT([Due Date],"mm/yyyy")
Turns due date into text without the day. Including the day does not work. - (DueDate+1M)
=DATE(YEAR([Due Date-Digit]),(MONTH([Due Date-Digit])-1),DAY([Due Date-Digit]))
Takes field 1 and turns it back to a date, minus 1 month. - (Start Month - DueDate+1M)
=IF(ISBLANK([DueDate+1M]),"",DATE(YEAR([DueDate+1M]),MONTH([DueDate+1M])+0,1))
Finds the 1st day of the month for field 2. - (End Month - DueDate+1M)
=IF(ISBLANK([Due Date]),"",DATE(YEAR([DueDate+1M]),MONTH([DueDate+1M])+1,0))
Finds the last day of the month for field 2.
Created a list view with the following filter.
I am sure there is an easier way to achieve the same result, but for now, I am happy with the outcome.
Thanks again, much appreciated.
Pete
- (Due Date-Digit)