Skip to main content

Hi all


I’m using a table to create a list of rotas showing who is on duty during a given date range. There will be multiple people on duty across different date ranges in different areas of the country. What I need is then to be able to use a search app in which users can enter any given date, and the results will show who is on duty on that date, and only those. I’ve only got as far as OR(IS_SAME(TODAY(), {Start}), IS_SAME(TODAY(), {Finish})) which gives “1” if {Start} is today’s date or if {Finish} is today’s date, and I think I’ll need the IS_SAME function at some point, but I’m stumped when it comes to getting the whole range of dates in one field.


My thinking for how to do this is as follows:




  1. Make a formula to show all dates individually that fall between the dates in a {Start} and a {Finish} field, i.e. IF {Start} is 27/11/2020 and {Finish} is 30/11/2020, the formula will display, “27/11/2020, 28/11/2020, 29/11/2020, 30/11/2020”. Call this field {Date range}.




  2. Use {Date range} in a second formula field to display “Yes/No” (or 1/0 will do fine) if TODAY is one of the dates in {Date range}. Call this field {On duty}




Then if users can use the search app to enter a date, and they are searching from within a view filtered by when {On duty} = “Yes”, the results will be only who is on duty in each area on that date.


I realise this might not be the easiest way to get the right data, formula wise, but it’s a shared base being used by people who are not all that familiar with Airtable, who need to be able to access the data as quickly and easily as possible, often at very quick notice (I’m building a base to support an emergency “out of hours” team). I know there are also calendar views that give the same information, but they get quite clogged up as there are over 40 different people “on duty” people on any given date.


Please note I’m in the UK so using the DD/MM/YYYY date format!

Hi @JonathanB - there’s a few tricks and one big problem with what you are trying to do. On the tricks side, once you have got the Date Range string you can do what you are asking:



In Airtable TODAY() is a date object so you need to parse this to a string to be able to compare it to the date range, so the {Today} field has a formula of:


DATETIME_FORMAT(TODAY(), 'DD/MM/YYYY')


Now, in the {On Duty} field, you can do:


IF(SEARCH(Today, {Date Range}), 1, 0)


The big problem you face is that you can’t use a formula to iterate over the items between a start and end date to get the date range string that you want. To do this you will need to use an Airtable script which could iterate between the dates and update a field with a string of all of the dates. This could be tied in with an automation to automatically produce this once the start and end dates have been entered.

@JonathanBowen If you're still hanging out on these community forums, do you have a suggestion for such a script as what you suggest above to iterate between the dates and update the Date Range field with a string of all the dates?

Reply


Terms & Conditions