Help

Filter by a specific day of last month

Topic Labels: Dates & Timezones
Solved
Jump to Solution
907 3
cancel
Showing results for 
Search instead for 
Did you mean: 
sehenley
5 - Automation Enthusiast
5 - Automation Enthusiast

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? 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

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

 

See Solution in Thread

3 Replies 3
Sho
11 - Venus
11 - Venus

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
4 - Data Explorer
4 - Data Explorer

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.