Filter by a specific day of last month

[sehenley]

I've set up a Table for recording my Staff working hours, moving from a paper system. Each day the staff member using a form, enters the date, name and number of hours worked, site location etc. 

When its time for payroll I want to create views that filter for last months payroll window. Our window runs from 25th of the month to 24th of next month. So when I'm preparing payroll on the 24th October, I want the view to filter out all results except those falling in 25th sept to 24th Oct pay window. 

I was trying to use filter so many days ago, but this varies each month depending if its 30 or 31 days. Is there a way of setting up a filter from the 25th of last month specifically? 

[Sho · Accepted solution]

It can be filtered by adding a formula field that calculates the year and month of payroll.

This formula displays the date in {Date} as the year and month, but after the 25th, it displays the year and month of the next month.

IF({Date},
  IF(
    {Date}<DATESTR(DATETIME_FORMAT({Date}, "YYYY/MM/25")),
    DATETIME_FORMAT({Date}, "YYYY/MM"),
    DATETIME_FORMAT(DATEADD({Date},1,"Month"),"YYYY/MM")
  )
)

[sehenley]

Hey that's really great thanks, I used that formula and then grouped by the formula results, it gave me exactly what I needed.

[andrewjeffery]

That's fantastic! Thanks a lot. I also use this formula and then grouped the results based on it, and it provided me with what I was looking for.