Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: FILTER BY NEXT WORK DAY, LAST WORK DAY

Solved
Jump to Solution
185 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Stephanie
4 - Data Explorer
4 - Data Explorer

I'm sure this an easy one - but I am stumped.  Here is the scenario: I want to create a view so I can send out a daily report that shows all records that are on tomorrow's service schedule, yesterday's service schedule, and 2 weeks ago service schedule.  The current airtable filter options of tomorrow and yesterday work great until it comes to the weekend - meaning on my Friday report, the Monday activity is not showing up and on my Monday report, the Friday activity is not showing up.  I have created formula fields that calculate the next work day but Im still stuck on how to filter it.  I tried to filter using "is after" "today" but then it gives me all records in the future when I just want the one next workday.  Maybe I am approaching this all wrong and making it more difficult than it is - but my brain is not computing 

1 Solution

Accepted Solutions
MatiasTN
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey Stephanie!

I believe you can solve this by using the WORKDAY_DIFF formula. (Counts the number of working days between startDate and endDate, inclusive. Working days exclude weekends.)

So you could build a formula field called "Workdays Difference" that calculates the workdays between the record's date field and today(). It would look like this: WORKDAY_DIFF(today(),{yourDateField})

And to display tomorrows and yesterdays records in a view, you should apply the filtersWorkdays Difference = 2 OR Workdays Difference = -2 (and the additional options that you were using to select the records from 2 weeks ago)

Why does the filter have to be equal to 2 or -2? Well I also found it strange, but that is how the WORKDAY_DIFF formula appears to work when I tested it out just now. Test the formula against your records and check if it works correctly and you receive the expected records for your report.

Let me know how it goes!
-Matt

See Solution in Thread

2 Replies 2
MatiasTN
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey Stephanie!

I believe you can solve this by using the WORKDAY_DIFF formula. (Counts the number of working days between startDate and endDate, inclusive. Working days exclude weekends.)

So you could build a formula field called "Workdays Difference" that calculates the workdays between the record's date field and today(). It would look like this: WORKDAY_DIFF(today(),{yourDateField})

And to display tomorrows and yesterdays records in a view, you should apply the filtersWorkdays Difference = 2 OR Workdays Difference = -2 (and the additional options that you were using to select the records from 2 weeks ago)

Why does the filter have to be equal to 2 or -2? Well I also found it strange, but that is how the WORKDAY_DIFF formula appears to work when I tested it out just now. Test the formula against your records and check if it works correctly and you receive the expected records for your report.

Let me know how it goes!
-Matt

Stephanie
4 - Data Explorer
4 - Data Explorer

Thank you so much for your response!  This worked perfectly!!  And I would have never thought to use the workday diff formula!  So appreciate your help!