One option that immediately comes to mind is to use the multi-date calendar feature that is available in “Pro” subscription workspaces. It can be configured to display a date range defined by two date fields (like you have) as a block across all dates spanning that range. This would give you a visual display of when items are out for rental.
Here’s a really simple example (be sure to look at the Calendar view in Table1):
https://airtable.com/shrQGmn0gFjC1ent9
I feel like this is probably what you are looking for, but if this does not work for you, I’m sure there are other ways accomplish what you want.
One option that immediately comes to mind is to use the multi-date calendar feature that is available in “Pro” subscription workspaces. It can be configured to display a date range defined by two date fields (like you have) as a block across all dates spanning that range. This would give you a visual display of when items are out for rental.
Here’s a really simple example (be sure to look at the Calendar view in Table1):
https://airtable.com/shrQGmn0gFjC1ent9
I feel like this is probably what you are looking for, but if this does not work for you, I’m sure there are other ways accomplish what you want.
Thanks Jeremy for your input.
I know about the Pro calendar feature, but apart from the premium costs I would really like to find a way to achieve this in the table (grid view) so I can further work with the dates rather than just displaying them in a calendar view. For example I want to establish a date collision detection if two events involving the same item occupy the same days etc. I found a somewhat OK workaround for the moment that allows me to display the date range if it is not in the past with IF(IS_AFTER({Date from}, TODAY())=1,CONCATENATE({Date from}, " - ",{Date to})) and then display all the range dates for an item with a simple lookup field. But this data is not ‘smart’ it’s just a string and not further workable with.
Thanks Jeremy for your input.
I know about the Pro calendar feature, but apart from the premium costs I would really like to find a way to achieve this in the table (grid view) so I can further work with the dates rather than just displaying them in a calendar view. For example I want to establish a date collision detection if two events involving the same item occupy the same days etc. I found a somewhat OK workaround for the moment that allows me to display the date range if it is not in the past with IF(IS_AFTER({Date from}, TODAY())=1,CONCATENATE({Date from}, " - ",{Date to})) and then display all the range dates for an item with a simple lookup field. But this data is not ‘smart’ it’s just a string and not further workable with.
Ya - that’s a tough one. You might have better luck using a
Start - Date field
and
Period - # Field (# of days rental will be for)
and then using combinations of
DATEADD()
DATETIME_PARSE()
and
DATETIME_FORMAT()
across multiple, conglomerating fields to create a string/array of dates. I think it’s possible. But it is convoluted too.
Yup, that’s an idea along the lines of what I was trying but I couldn’t wrap my head around it yet.
Yup, that’s an idea along the lines of what I was trying but I couldn’t wrap my head around it yet.
OK: Let me start by saying that just because I’m showing you how to do this, it doesn’t mean I think you should do this. :winking_face: (Later, I’ll show you what I think you should do.)
The problem with calculating a span of any time is the lack of a looping mechanism in Airtable. As a result, one has to state explicitly every unit of time one wishes to test. For units of a year, it’s doable. For a month, maybe so. (For a month/year combo, less so.)
For units of a day, you’re talking a seriously ugly formula.
However, I’ve recently started experimenting with letting Airtable write its own seriously ugly formulas. Even though it provides you with what you requested, I see this base as more an example of a self-generated formula than a practical base.
A couple things to keep in mind:
This base does two things: First, and primarily, it generates text that will then be copy-and-pasted into the formula configuration window of a formula field. Second, it demonstrates the resulting formula field correctly generates an array of dates from {StartDate} through {EndDate}, including all intervening dates.
Your original post specified 'MM/DD' format for the date array; however, the dates need to specify the year, as well, to support time spans that straddle January 1. This, of course, greatly complicates matters.
As provided here, the resulting array is in the format
MM/DD/YYYY|MM/DD/YYYY|...
This allows one to roll up all the pertinent arrays and test for a given date with
FIND('MM/DD/YYYY|',{ArrayRollup})
That is, the string to match should have the separating character, the vertical bar, appended. Should a different separating character be desired, one will need to generate a new formula, as described below.
The date array for time spans that straddle January 1 are not provided in date order. Instead, intervening dates from January 1 through {EndDate} are provided, followed by intervening dates from {StartDate} through December 31. Should this not be acceptable, you are more than welcome to figure out a solution.
No explicit support is provided for Leap Year; however, the algorithm will work as long as neither {StartDate} nor {EndDate} is set to February 29.
The base contains a single table, aTable 1]. The first three fields in tTable 1] are used to create the text that makes up the formula in a fourth field, {Span}:
{Date}. This is a date field, running sequentially from 1/1/2018 through 12/31/2018. (To create it, 365 blank records were created by copy-and-pasting progressively larger blocks of empty records until the desired total had been reached. Values were then manually entered for Rows 1 and 2; those two cells were then selected, and the fill handle at the lower right corner of the cell for Row 2 was then selected and dragged to the bottom of the table, filling the remaining 363 cells sequentially.)
{MonthDay}. This is a formula field with the formula
DATETIME_FORMAT({Date},'MM/DD')
The resulting 'MM/DD' string is used in calculations to create text for the ultimate formula.
{Snippet}. This is the workhorse of the base. Since, as mentioned earlier, Airtable has no looping mechanism, each day of the year must be tested explicitly for inclusion; the desired array, then, is simply all dates that prove to be intervening dates concatenated together. Accordingly, the formula in {Snippet} generates a, well, snippet of Airtable code that performs such a test for a single day. In addition, each snippet ends with an ampersand character ('&') appended as something of an open-ended concatenation, as explained below.
There are two things to note about the {Snippet} formula: First, the precedence of single and double quotes shown must be maintained; otherwise, Airtable will attempt to wrap quoted sections with additional double quotes, corrupting the syntax. Second, also to prevent Airtable from attempting to apply additional formatting to the output string, the formula must be included as a single, run-on string, without line breaks or indentation. (Well, I guess one could include indentation without line breaks, but that would be even more of a mess to decipher…)
The formula for {Snippet} is as follows:
"IF(AND(DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))>=StartDate,DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))<=EndDate),'"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate)))&'|')&"
What that does is easier to suss out by inspecting the resulting formula snippet for a given day, in this case, January 1:
IF(
AND(
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)>=StartDate,
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)<=EndDate
),
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)&
'|'
)&
(Well, OK, so maybe it’s not all that much easier…)
The resulting snippets for January 1 through December 31 are then used to create the formula configured for the {Span} field. To do so
- Select the cell for
{Snippet} in Row 1.
- Scroll to the bottom of the table and, while holding down the
Shift key, select {Snippet} in Row 365.
- Press
Ctrl-C to copy all {Snippet} values.
- Right-click on
{Span} and select ‘Customize field type’.
- Click within the ‘Formula’ window.
- Press
Ctrl-A to mark All of the existing formula text.
- Press
Ctrl-V to paste the copied contents of all 365 {Snippet} fields into the formula window for {Span}.
Important: After the paste is completed, the cursor will be placed following the ampersand character ('&') at the end of the snippet for December 31. Press backspace once to delete this '&'.
- Click
'Save'. If everything has gone smoothly, after a brief delay, {Span} should accept the new formula configuration without generating a red ‘invalid formula’ error.
Note: In the context of this base, {Span} in every record where {StartDate} and/or {EndDate} are undefined will display #ERROR!. This is to be expected.
To demonstrate {Span} works as intended, define {StartDate} and {EndDate} for a record. Once both dates have been entered, Airtable whirs for a moment and then outputs, in {Span}, an array of dates beginning with {StartDate} and continuing through {EndDate}, with all intervening dates included. (The base assumes {EndDate} is at least one day laterb than {StartDate}. No checks are performed to ensure {EndDate} actually is later than {StartDate}.)
———but as I said earlier, you don’t really want to do this. What do you really want to do? Well, I’ll tell you shortly, after I deal with a pending interruption. (My landlord is selling the house out from beneath me, so today I’m playing host to two home inspectors, a photographer, a draftsman, the Realtor
, and, yes, the landlord. Back shortly.)
OK: Let me start by saying that just because I’m showing you how to do this, it doesn’t mean I think you should do this. :winking_face: (Later, I’ll show you what I think you should do.)
The problem with calculating a span of any time is the lack of a looping mechanism in Airtable. As a result, one has to state explicitly every unit of time one wishes to test. For units of a year, it’s doable. For a month, maybe so. (For a month/year combo, less so.)
For units of a day, you’re talking a seriously ugly formula.
However, I’ve recently started experimenting with letting Airtable write its own seriously ugly formulas. Even though it provides you with what you requested, I see this base as more an example of a self-generated formula than a practical base.
A couple things to keep in mind:
This base does two things: First, and primarily, it generates text that will then be copy-and-pasted into the formula configuration window of a formula field. Second, it demonstrates the resulting formula field correctly generates an array of dates from {StartDate} through {EndDate}, including all intervening dates.
Your original post specified 'MM/DD' format for the date array; however, the dates need to specify the year, as well, to support time spans that straddle January 1. This, of course, greatly complicates matters.
As provided here, the resulting array is in the format
MM/DD/YYYY|MM/DD/YYYY|...
This allows one to roll up all the pertinent arrays and test for a given date with
FIND('MM/DD/YYYY|',{ArrayRollup})
That is, the string to match should have the separating character, the vertical bar, appended. Should a different separating character be desired, one will need to generate a new formula, as described below.
The date array for time spans that straddle January 1 are not provided in date order. Instead, intervening dates from January 1 through {EndDate} are provided, followed by intervening dates from {StartDate} through December 31. Should this not be acceptable, you are more than welcome to figure out a solution.
No explicit support is provided for Leap Year; however, the algorithm will work as long as neither {StartDate} nor {EndDate} is set to February 29.
The base contains a single table, nTable 1]. The first three fields in eTable 1] are used to create the text that makes up the formula in a fourth field, {Span}:
{Date}. This is a date field, running sequentially from 1/1/2018 through 12/31/2018. (To create it, 365 blank records were created by copy-and-pasting progressively larger blocks of empty records until the desired total had been reached. Values were then manually entered for Rows 1 and 2; those two cells were then selected, and the fill handle at the lower right corner of the cell for Row 2 was then selected and dragged to the bottom of the table, filling the remaining 363 cells sequentially.)
{MonthDay}. This is a formula field with the formula
DATETIME_FORMAT({Date},'MM/DD')
The resulting 'MM/DD' string is used in calculations to create text for the ultimate formula.
{Snippet}. This is the workhorse of the base. Since, as mentioned earlier, Airtable has no looping mechanism, each day of the year must be tested explicitly for inclusion; the desired array, then, is simply all dates that prove to be intervening dates concatenated together. Accordingly, the formula in {Snippet} generates a, well, snippet of Airtable code that performs such a test for a single day. In addition, each snippet ends with an ampersand character ('&') appended as something of an open-ended concatenation, as explained below.
There are two things to note about the {Snippet} formula: First, the precedence of single and double quotes shown must be maintained; otherwise, Airtable will attempt to wrap quoted sections with additional double quotes, corrupting the syntax. Second, also to prevent Airtable from attempting to apply additional formatting to the output string, the formula must be included as a single, run-on string, without line breaks or indentation. (Well, I guess one could include indentation without line breaks, but that would be even more of a mess to decipher…)
The formula for {Snippet} is as follows:
"IF(AND(DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))>=StartDate,DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))<=EndDate),'"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate)))&'|')&"
What that does is easier to suss out by inspecting the resulting formula snippet for a given day, in this case, January 1:
IF(
AND(
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)>=StartDate,
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)<=EndDate
),
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)&
'|'
)&
(Well, OK, so maybe it’s not all that much easier…)
The resulting snippets for January 1 through December 31 are then used to create the formula configured for the {Span} field. To do so
- Select the cell for
{Snippet} in Row 1.
- Scroll to the bottom of the table and, while holding down the
Shift key, select {Snippet} in Row 365.
- Press
Ctrl-C to copy all {Snippet} values.
- Right-click on
{Span} and select ‘Customize field type’.
- Click within the ‘Formula’ window.
- Press
Ctrl-A to mark All of the existing formula text.
- Press
Ctrl-V to paste the copied contents of all 365 {Snippet} fields into the formula window for {Span}.
Important: After the paste is completed, the cursor will be placed following the ampersand character ('&') at the end of the snippet for December 31. Press backspace once to delete this '&'.
- Click
'Save'. If everything has gone smoothly, after a brief delay, {Span} should accept the new formula configuration without generating a red ‘invalid formula’ error.
Note: In the context of this base, {Span} in every record where {StartDate} and/or {EndDate} are undefined will display #ERROR!. This is to be expected.
To demonstrate {Span} works as intended, define {StartDate} and {EndDate} for a record. Once both dates have been entered, Airtable whirs for a moment and then outputs, in {Span}, an array of dates beginning with {StartDate} and continuing through {EndDate}, with all intervening dates included. (The base assumes {EndDate} is at least one day laterb than {StartDate}. No checks are performed to ensure {EndDate} actually is later than {StartDate}.)
———but as I said earlier, you don’t really want to do this. What do you really want to do? Well, I’ll tell you shortly, after I deal with a pending interruption. (My landlord is selling the house out from beneath me, so today I’m playing host to two home inspectors, a photographer, a draftsman, the Realtor, and, yes, the landlord. Back shortly.)
OK: Let me start by saying that just because I’m showing you how to do this, it doesn’t mean I think you should do this. :winking_face: (Later, I’ll show you what I think you should do.)
The problem with calculating a span of any time is the lack of a looping mechanism in Airtable. As a result, one has to state explicitly every unit of time one wishes to test. For units of a year, it’s doable. For a month, maybe so. (For a month/year combo, less so.)
For units of a day, you’re talking a seriously ugly formula.
However, I’ve recently started experimenting with letting Airtable write its own seriously ugly formulas. Even though it provides you with what you requested, I see this base as more an example of a self-generated formula than a practical base.
A couple things to keep in mind:
This base does two things: First, and primarily, it generates text that will then be copy-and-pasted into the formula configuration window of a formula field. Second, it demonstrates the resulting formula field correctly generates an array of dates from {StartDate} through {EndDate}, including all intervening dates.
Your original post specified 'MM/DD' format for the date array; however, the dates need to specify the year, as well, to support time spans that straddle January 1. This, of course, greatly complicates matters.
As provided here, the resulting array is in the format
MM/DD/YYYY|MM/DD/YYYY|...
This allows one to roll up all the pertinent arrays and test for a given date with
FIND('MM/DD/YYYY|',{ArrayRollup})
That is, the string to match should have the separating character, the vertical bar, appended. Should a different separating character be desired, one will need to generate a new formula, as described below.
The date array for time spans that straddle January 1 are not provided in date order. Instead, intervening dates from January 1 through {EndDate} are provided, followed by intervening dates from {StartDate} through December 31. Should this not be acceptable, you are more than welcome to figure out a solution.
No explicit support is provided for Leap Year; however, the algorithm will work as long as neither {StartDate} nor {EndDate} is set to February 29.
The base contains a single table, nTable 1]. The first three fields in eTable 1] are used to create the text that makes up the formula in a fourth field, {Span}:
{Date}. This is a date field, running sequentially from 1/1/2018 through 12/31/2018. (To create it, 365 blank records were created by copy-and-pasting progressively larger blocks of empty records until the desired total had been reached. Values were then manually entered for Rows 1 and 2; those two cells were then selected, and the fill handle at the lower right corner of the cell for Row 2 was then selected and dragged to the bottom of the table, filling the remaining 363 cells sequentially.)
{MonthDay}. This is a formula field with the formula
DATETIME_FORMAT({Date},'MM/DD')
The resulting 'MM/DD' string is used in calculations to create text for the ultimate formula.
{Snippet}. This is the workhorse of the base. Since, as mentioned earlier, Airtable has no looping mechanism, each day of the year must be tested explicitly for inclusion; the desired array, then, is simply all dates that prove to be intervening dates concatenated together. Accordingly, the formula in {Snippet} generates a, well, snippet of Airtable code that performs such a test for a single day. In addition, each snippet ends with an ampersand character ('&') appended as something of an open-ended concatenation, as explained below.
There are two things to note about the {Snippet} formula: First, the precedence of single and double quotes shown must be maintained; otherwise, Airtable will attempt to wrap quoted sections with additional double quotes, corrupting the syntax. Second, also to prevent Airtable from attempting to apply additional formatting to the output string, the formula must be included as a single, run-on string, without line breaks or indentation. (Well, I guess one could include indentation without line breaks, but that would be even more of a mess to decipher…)
The formula for {Snippet} is as follows:
"IF(AND(DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))>=StartDate,DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))<=EndDate),'"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate)))&'|')&"
What that does is easier to suss out by inspecting the resulting formula snippet for a given day, in this case, January 1:
IF(
AND(
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)>=StartDate,
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)<=EndDate
),
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)&
'|'
)&
(Well, OK, so maybe it’s not all that much easier…)
The resulting snippets for January 1 through December 31 are then used to create the formula configured for the {Span} field. To do so
- Select the cell for
{Snippet} in Row 1.
- Scroll to the bottom of the table and, while holding down the
Shift key, select {Snippet} in Row 365.
- Press
Ctrl-C to copy all {Snippet} values.
- Right-click on
{Span} and select ‘Customize field type’.
- Click within the ‘Formula’ window.
- Press
Ctrl-A to mark All of the existing formula text.
- Press
Ctrl-V to paste the copied contents of all 365 {Snippet} fields into the formula window for {Span}.
Important: After the paste is completed, the cursor will be placed following the ampersand character ('&') at the end of the snippet for December 31. Press backspace once to delete this '&'.
- Click
'Save'. If everything has gone smoothly, after a brief delay, {Span} should accept the new formula configuration without generating a red ‘invalid formula’ error.
Note: In the context of this base, {Span} in every record where {StartDate} and/or {EndDate} are undefined will display #ERROR!. This is to be expected.
To demonstrate {Span} works as intended, define {StartDate} and {EndDate} for a record. Once both dates have been entered, Airtable whirs for a moment and then outputs, in {Span}, an array of dates beginning with {StartDate} and continuing through {EndDate}, with all intervening dates included. (The base assumes {EndDate} is at least one day laterb than {StartDate}. No checks are performed to ensure {EndDate} actually is later than {StartDate}.)
———but as I said earlier, you don’t really want to do this. What do you really want to do? Well, I’ll tell you shortly, after I deal with a pending interruption. (My landlord is selling the house out from beneath me, so today I’m playing host to two home inspectors, a photographer, a draftsman, the Realtor, and, yes, the landlord. Back shortly.)
WOW
Thank you so much for taking the time to provide this very clever approach. It will take me some time to understand what you did there exactly though. This is very impressive.
I’m looking forward to hear what your recommended way of doing this will be.
Oh, and good luck with the house selling business and your landlord.
WOW
Thank you so much for taking the time to provide this very clever approach. It will take me some time to understand what you did there exactly though. This is very impressive.
I’m looking forward to hear what your recommended way of doing this will be.
Oh, and good luck with the house selling business and your landlord.
pSorry for the delay: First, I was distracted. Then I discovered Airtable doesn’t work the way I thought it did (which is, of course, the way it should work. :winking_face: ) Then I grudgingly began to write up this approach and realized a different work flow made more sense. Then I forgot I hadn’t finished this and answered some other questions. And then I had no more excuses.]
So, despite how much fun it was to create that earlier monstrosity, here’s what I think you should really do.
Caveat: When I started this Thursday night, there was no use case posted — so I made one up for you. It just occurred to me you may have since posted more about your application or your process. If so, I apologize, as I am going to pretend you haven’t, as it makes things easier for me…
My Made-up Version of Your Workflow
A customer calls and wishes to lease a piece of equipment, a widget, for a specific period of time. For argument’s sake, let’s stipulate this is a brand-new widget and as such has never been leased before. Your employee makes a note of the rental period’s start and end dates, calculates the rental price accordingly, and books the reservation.
However, word is out you carry the absolute latest-model widget in existence, and almost immediately another customer calls, looking to lease it. Your employee checks the requested beginning and end dates for potential overlap with the existing booking. If no overlap exists, he or she books the second reservation; if the two requested time spans do overlap, the employee works with the customer to identify an alternative schedule that meets his or her needs.
Your Faux Workflow Mapped Against Recommended Base
Just to confuse the heck out of people who accessed the base in the interval between my first reply and this one, my recommended approach can be found — surprise! —in the same base as the seriously ugly approach. Since my initial reply, the base has grown two additional tables, dEquipment] and pRentals]. In nEquipment], you will find two date fields. {Req uested] Start Date} and {Req End Date}; you will also find the {Alert} field, which displays an error message when the requested date span overlaps an existing reservation.
To return to the scenario from my made-up version of your workflow, when Customer 1 calls to schedule widget rental, using this base the employee would enter the requested start and end dates in the applicable field. As no error message appears in {Alert} — unsurprisingly, as no earlier rental exists to conflict with the newly requested one — the employee proceeds to generate a new rental for Customer 1. To do so, he or she
- selects THE
{Req>uested] Span} field
- presses
Ctrl-C to copy its value
- selects the
plus sign ('+') in the {Link to Rentals} field, drilling through to the lRentals] table to display a list of existing rentals
- selects
'+ Add new record', creating a new oRentals] record and opening it for data entry
- selects the
{Rental Span} field
- presses
Ctrl-V to paste the value into {Rentals::Rental Span}
Once {Rental Span} is filled, {Rentals::Start Date} and {Rentals::End Date} are populated, as well. The employee then proceeds to enter pertinent data (customer name, address, phone, payment info, name of first-born son, and so forth) in fields I didn’t bother to create.
When Customer 2 calls, your employee once again enters the requested start and end dates. (After the aRentals] record has been created, {Equipment::Req Start Date} and {Equipment::Req End Date} can be cleared. However, I’ve tried to structure {Alert}'s logic in such a way they don’t have to be: The employee can overwrite the previous requested dates with the new ones.) However, should either requested date fall within the span of an existing rental, a message appears in {Alert} notifying the user and offering some guidance as to how the requested rental should be shifted. (For example, if {Req Start Date} falls within another rental, {Alert} will read 
Bad start date - Widget unavailable until 05-16-18
Once the employee and customer have negotiated a non-conflicting rental span, the employee creates and populates a sRentals] record as before.
Caveats: The system as published assumes equipment will be returned at COB {End Date} and issued at opening of business {Start Date}; no provision is made for either overlapping {End Date}/{Start Date} or for between-rental maintenance. No explicit provision is made for equipment returned prior to {End Date} (although retroactively changing {End Date} should prevent later rental requests from being blocked inappropriately). To prevent invalid alarming if requested dates are entered into already-populated {Req xxx Date} fields, the system ignores instances where {End Date} falls before {Start Date} (although I do throw an alarm if an attempt is made to configure a newly created record as such).
What I Wish It Could Do
Originally, my plans were to have all >Rentals] records to be generated by a three-keystroke copy/select/paste sequence. (This also required sRentals]’ primary field to be {Rental Span}.) Unfortunately, while such a sequence works fine for the first linked record created, subsequent actions overwrite the existing field rather than create a new one. Since all but the first record creation would have to be performed manually, I chose to require them all to be performed manually, gaining a little more informative linked record value in the tradeoff. Record creation would be even more streamlined if Airtable didn’t absurdly always make the primary field of a newly opened record to be the active field — even, as in this case, if the primary field does not allow data entry. If it instead made the first editable field active, things would flow more smoothly.
[Sorry for the delay: First, I was distracted. Then I discovered Airtable doesn’t work the way I thought it did (which is, of course, the way it should work. :winking_face: ) Then I grudgingly began to write up this approach and realized a different work flow made more sense. Then I forgot I hadn’t finished this and answered some other questions. And then I had no more excuses.]
So, despite how much fun it was to create that earlier monstrosity, here’s what I think you should really do.
Caveat: When I started this Thursday night, there was no use case posted — so I made one up for you. It just occurred to me you may have since posted more about your application or your process. If so, I apologize, as I am going to pretend you haven’t, as it makes things easier for me…
My Made-up Version of Your Workflow
A customer calls and wishes to lease a piece of equipment, a widget, for a specific period of time. For argument’s sake, let’s stipulate this is a brand-new widget and as such has never been leased before. Your employee makes a note of the rental period’s start and end dates, calculates the rental price accordingly, and books the reservation.
However, word is out you carry the absolute latest-model widget in existence, and almost immediately another customer calls, looking to lease it. Your employee checks the requested beginning and end dates for potential overlap with the existing booking. If no overlap exists, he or she books the second reservation; if the two requested time spans do overlap, the employee works with the customer to identify an alternative schedule that meets his or her needs.
Your Faux Workflow Mapped Against Recommended Base
Just to confuse the heck out of people who accessed the base in the interval between my first reply and this one, my recommended approach can be found — surprise! —in the same base as the seriously ugly approach. Since my initial reply, the base has grown two additional tables, iEquipment] and eRentals]. In aEquipment], you will find two date fields. {Reqauested] Start Date} and {Req End Date}; you will also find the {Alert} field, which displays an error message when the requested date span overlaps an existing reservation.
To return to the scenario from my made-up version of your workflow, when Customer 1 calls to schedule widget rental, using this base the employee would enter the requested start and end dates in the applicable field. As no error message appears in {Alert} — unsurprisingly, as no earlier rental exists to conflict with the newly requested one — the employee proceeds to generate a new rental for Customer 1. To do so, he or she
- selects THE
{Reqbuested] Span} field
- presses
Ctrl-C to copy its value
- selects the
plus sign ('+') in the {Link to Rentals} field, drilling through to the ,Rentals] table to display a list of existing rentals
- selects
'+ Add new record', creating a new dRentals] record and opening it for data entry
- selects the
{Rental Span} field
- presses
Ctrl-V to paste the value into {Rentals::Rental Span}
Once {Rental Span} is filled, {Rentals::Start Date} and {Rentals::End Date} are populated, as well. The employee then proceeds to enter pertinent data (customer name, address, phone, payment info, name of first-born son, and so forth) in fields I didn’t bother to create.
When Customer 2 calls, your employee once again enters the requested start and end dates. (After the tRentals] record has been created, {Equipment::Req Start Date} and {Equipment::Req End Date} can be cleared. However, I’ve tried to structure {Alert}'s logic in such a way they don’t have to be: The employee can overwrite the previous requested dates with the new ones.) However, should either requested date fall within the span of an existing rental, a message appears in {Alert} notifying the user and offering some guidance as to how the requested rental should be shifted. (For example, if {Req Start Date} falls within another rental, {Alert} will read Bad start date - Widget unavailable until 05-16-18.) If there is more than one existing rental, the requested dates are checked against each.
Once the employee and customer have negotiated a non-conflicting rental span, the employee creates and populates a aRentals] record as before.
Caveats: The system as published assumes equipment will be returned at COB {End Date} and issued at opening of business {Start Date}; no provision is made for either overlapping {End Date}/{Start Date} or for between-rental maintenance. No explicit provision is made for equipment returned prior to {End Date} (although retroactively changing {End Date} should prevent later rental requests from being blocked inappropriately). To prevent invalid alarming if requested dates are entered into already-populated {Req xxx Date} fields, the system ignores instances where {End Date} falls before {Start Date} (although I do throw an alarm if an attempt is made to configure a newly created record as such).
What I Wish It Could Do
Originally, my plans were to have all records to be generated by a three-keystroke copy/select/paste sequence. (This also required lRentals]’ primary field to be {Rental Span}.) Unfortunately, while such a sequence works fine for the first linked record created, subsequent actions overwrite the existing field rather than create a new one. Since all but the first record creation would have to be performed manually, I chose to require them all to be performed manually, gaining a little more informative linked record value in the tradeoff. Record creation would be even more streamlined if Airtable didn’t absurdly always make the primary field of a newly opened record to be the active field — even, as in this case, if the primary field does not allow data entry. If it instead made the first editable field active, things would flow more smoothly.
Thank you so much again!
I like your solution, it’s very smart although it takes some extra steps to check for the collision upfront. What makes things a little bit more complicated is that we normally give away a set of equipment consisting of multiple items. So a variation of your first (more convoluted) workflow will probably be ‘easier’ to implement into our workflow for me.
No matter what the final solution will be you helped a lot to get me on the right track. You also confirmed my suspicion that Airtables current limitations regarding dates and time spans makes it rather impractical to use it for scheduling purposes.
Again, thanks a lot for your help and time!
btw, how did your house selling appointment go?
Thank you so much again!
I like your solution, it’s very smart although it takes some extra steps to check for the collision upfront. What makes things a little bit more complicated is that we normally give away a set of equipment consisting of multiple items. So a variation of your first (more convoluted) workflow will probably be ‘easier’ to implement into our workflow for me.
No matter what the final solution will be you helped a lot to get me on the right track. You also confirmed my suspicion that Airtables current limitations regarding dates and time spans makes it rather impractical to use it for scheduling purposes.
Again, thanks a lot for your help and time!
btw, how did your house selling appointment go?
Fine, I guess. Agents Open House this coming Tuesday; buyers’ open houses on Wednesday, Thursday, and following Tuesday, 90 - 120 minutes each day. The assumption is that it will have sold by next Tuesday.
But we’re rent-controlled, so virtually any unpleasant scenario ends with our getting bought out. It’s not the type of property (1876 house divided into two flats, plus a faux carriage house cottage circa 1972) that would lend itself to an owner move-in, but if they could get us toleave, they could immediately triple the rent; with even minimal renovation, quadruple, maybe quintuple it. Still not entered into MLS, so I’m not sure what they’re asking.
Thank you so much again!
I like your solution, it’s very smart although it takes some extra steps to check for the collision upfront. What makes things a little bit more complicated is that we normally give away a set of equipment consisting of multiple items. So a variation of your first (more convoluted) workflow will probably be ‘easier’ to implement into our workflow for me.
No matter what the final solution will be you helped a lot to get me on the right track. You also confirmed my suspicion that Airtables current limitations regarding dates and time spans makes it rather impractical to use it for scheduling purposes.
Again, thanks a lot for your help and time!
btw, how did your house selling appointment go?
Mark -
I have a couple of questions to ask about your application. Could you message me here or at wvannhallnat]paladesignsedot]com with a way to reach you?
Thanks!
Hi @W_Vann_Hall,
I would like to just do a simple span with no Month day.
My workflow:
A new record is created with a {Start Date} and a {End Date} in Table 1.
For each date between those dates I would like to create a new record in Table 2.
The logistics for creating new records happen in Integromat. I just need the Span of dates (An Array of Dates from Airtable).
How could I achieve this? I think I’m losing my mind, because I don’t see loops in Airtable.
Hi @W_Vann_Hall,
I would like to just do a simple span with no Month day.
My workflow:
A new record is created with a {Start Date} and a {End Date} in Table 1.
For each date between those dates I would like to create a new record in Table 2.
The logistics for creating new records happen in Integromat. I just need the Span of dates (An Array of Dates from Airtable).
How could I achieve this? I think I’m losing my mind, because I don’t see loops in Airtable.
You’re right: there are no loops in Airtable (yet).
Is there a maximum span to the date range? If so, there’s a relatively easy way to generate a group of new records, one per date in the range. In fact, I do exactly that in my scheduling framework. (The published base assumes a maximum range of 30 days, but that’s easily modified.) Take a look at the configuration of {SubtaskName} in the tTo Do] table; there’s a part of that formula that generates a series of subtask names based on a date range of some stated duration. The subtasks are named '02: MasterTask name]', '03: MasterTask name]', and so on. To generate the records in your Table 2] — assuming they are records linked from eTable 1] — just have Integromat copy the value of your implementation of {SubtaskName} and paste it into the linked-record field; that will cause Airtable to create new records in eTable 2]. (If there’s no linkage between tables, your task will be a little harder — but you’re clearly comfortable enough with Integromat.)
The code that generates the list of record names should be flexible enough to incorporate your naming convention. You’ll need to include support for Day 1, as I use the master task itself for that.
If you need an unlimited date range, though, you’re on your own. :winking_face:
Thank you so much again!
I like your solution, it’s very smart although it takes some extra steps to check for the collision upfront. What makes things a little bit more complicated is that we normally give away a set of equipment consisting of multiple items. So a variation of your first (more convoluted) workflow will probably be ‘easier’ to implement into our workflow for me.
No matter what the final solution will be you helped a lot to get me on the right track. You also confirmed my suspicion that Airtables current limitations regarding dates and time spans makes it rather impractical to use it for scheduling purposes.
Again, thanks a lot for your help and time!
btw, how did your house selling appointment go?
Hello,
I am actually looking for the same solution, but for hotel check-in / check-out dates.
I need that formula to generate a report with inhouse guests for specific dates.
A third multi-selection field genereted from Date-IN and Date-OUT would be perfect for me.
Did you finally get a solution?
Thanks
OK: Let me start by saying that just because I’m showing you how to do this, it doesn’t mean I think you should do this. :winking_face: (Later, I’ll show you what I think you should do.)
The problem with calculating a span of any time is the lack of a looping mechanism in Airtable. As a result, one has to state explicitly every unit of time one wishes to test. For units of a year, it’s doable. For a month, maybe so. (For a month/year combo, less so.)
For units of a day, you’re talking a seriously ugly formula.
However, I’ve recently started experimenting with letting Airtable write its own seriously ugly formulas. Even though it provides you with what you requested, I see this base as more an example of a self-generated formula than a practical base.
A couple things to keep in mind:
This base does two things: First, and primarily, it generates text that will then be copy-and-pasted into the formula configuration window of a formula field. Second, it demonstrates the resulting formula field correctly generates an array of dates from {StartDate} through {EndDate}, including all intervening dates.
Your original post specified 'MM/DD' format for the date array; however, the dates need to specify the year, as well, to support time spans that straddle January 1. This, of course, greatly complicates matters.
As provided here, the resulting array is in the format
MM/DD/YYYY|MM/DD/YYYY|...
This allows one to roll up all the pertinent arrays and test for a given date with
FIND('MM/DD/YYYY|',{ArrayRollup})
That is, the string to match should have the separating character, the vertical bar, appended. Should a different separating character be desired, one will need to generate a new formula, as described below.
The date array for time spans that straddle January 1 are not provided in date order. Instead, intervening dates from January 1 through {EndDate} are provided, followed by intervening dates from {StartDate} through December 31. Should this not be acceptable, you are more than welcome to figure out a solution.
No explicit support is provided for Leap Year; however, the algorithm will work as long as neither {StartDate} nor {EndDate} is set to February 29.
The base contains a single table, nTable 1]. The first three fields in eTable 1] are used to create the text that makes up the formula in a fourth field, {Span}:
{Date}. This is a date field, running sequentially from 1/1/2018 through 12/31/2018. (To create it, 365 blank records were created by copy-and-pasting progressively larger blocks of empty records until the desired total had been reached. Values were then manually entered for Rows 1 and 2; those two cells were then selected, and the fill handle at the lower right corner of the cell for Row 2 was then selected and dragged to the bottom of the table, filling the remaining 363 cells sequentially.)
{MonthDay}. This is a formula field with the formula
DATETIME_FORMAT({Date},'MM/DD')
The resulting 'MM/DD' string is used in calculations to create text for the ultimate formula.
{Snippet}. This is the workhorse of the base. Since, as mentioned earlier, Airtable has no looping mechanism, each day of the year must be tested explicitly for inclusion; the desired array, then, is simply all dates that prove to be intervening dates concatenated together. Accordingly, the formula in {Snippet} generates a, well, snippet of Airtable code that performs such a test for a single day. In addition, each snippet ends with an ampersand character ('&') appended as something of an open-ended concatenation, as explained below.
There are two things to note about the {Snippet} formula: First, the precedence of single and double quotes shown must be maintained; otherwise, Airtable will attempt to wrap quoted sections with additional double quotes, corrupting the syntax. Second, also to prevent Airtable from attempting to apply additional formatting to the output string, the formula must be included as a single, run-on string, without line breaks or indentation. (Well, I guess one could include indentation without line breaks, but that would be even more of a mess to decipher…)
The formula for {Snippet} is as follows:
"IF(AND(DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))>=StartDate,DATETIME_PARSE('"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate))))<=EndDate),'"&MonthDay&"/'&IF(YEAR(StartDate)=YEAR(EndDate),YEAR(StartDate),IF('"&MonthDay&"'<DATETIME_FORMAT(StartDate,'MM/DD'),YEAR(EndDate),YEAR(StartDate)))&'|')&"
What that does is easier to suss out by inspecting the resulting formula snippet for a given day, in this case, January 1:
IF(
AND(
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)>=StartDate,
DATETIME_PARSE(
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)
)<=EndDate
),
'01/01/'&
IF(
YEAR(StartDate)=YEAR(EndDate),
YEAR(StartDate),
IF(
'01/01'<DATETIME_FORMAT(
StartDate,
'MM/DD'
),
YEAR(EndDate),
YEAR(StartDate)
)
)&
'|'
)&
(Well, OK, so maybe it’s not all that much easier…)
The resulting snippets for January 1 through December 31 are then used to create the formula configured for the {Span} field. To do so
- Select the cell for
{Snippet} in Row 1.
- Scroll to the bottom of the table and, while holding down the
Shift key, select {Snippet} in Row 365.
- Press
Ctrl-C to copy all {Snippet} values.
- Right-click on
{Span} and select ‘Customize field type’.
- Click within the ‘Formula’ window.
- Press
Ctrl-A to mark All of the existing formula text.
- Press
Ctrl-V to paste the copied contents of all 365 {Snippet} fields into the formula window for {Span}.
Important: After the paste is completed, the cursor will be placed following the ampersand character ('&') at the end of the snippet for December 31. Press backspace once to delete this '&'.
- Click
'Save'. If everything has gone smoothly, after a brief delay, {Span} should accept the new formula configuration without generating a red ‘invalid formula’ error.
Note: In the context of this base, {Span} in every record where {StartDate} and/or {EndDate} are undefined will display #ERROR!. This is to be expected.
To demonstrate {Span} works as intended, define {StartDate} and {EndDate} for a record. Once both dates have been entered, Airtable whirs for a moment and then outputs, in {Span}, an array of dates beginning with {StartDate} and continuing through {EndDate}, with all intervening dates included. (The base assumes {EndDate} is at least one day laterb than {StartDate}. No checks are performed to ensure {EndDate} actually is later than {StartDate}.)
———but as I said earlier, you don’t really want to do this. What do you really want to do? Well, I’ll tell you shortly, after I deal with a pending interruption. (My landlord is selling the house out from beneath me, so today I’m playing host to two home inspectors, a photographer, a draftsman, the Realtor, and, yes, the landlord. Back shortly.)
Alright, so, I'm asking for help because really i've just tried to do the 100th formula and it just doesn't work 😭, so seeing your so kind, beautifuly detailed response, i'm taking my chances by asking for help.
See, what i'm looking for is this:
I need to have a calendar view to control a car workshop. The idea is that my call center will receive the calls from my clients, and then, in a record, upload the information of the client (regulars such as which work has to be done, which mechanic will do the job, and so, and so) and the idea of this calendar is:
To use this view to see if there's any open gap in the workshop, so if there's a gap between 11am and 1pm (for example), and the client in the phone is asking for a job that can be done in whitin that time span that's available, then the girl helping me can offer that space for the client. That way the mechanics will have more productive hours.
I've tried this:
Using one grid and doing manually about 36 columns (each one represents 15 minutes), and asking for my call center to fill each cell until the work is easily seen in this grid (she used a dropdown colored options field, and each color represented a different job). The problem with this is that i have 3 mechanics, so to fill the time of each mechanic i had to create 3 rows x 36 columns. BUT THEN, OMG, the mechanics work 5 days each week. So i created a date field (no time specification) and i had to group by this date field type each day of the year, so that in each group i had: one day, that had in it 3 rows (one for each mechanic), and each row had 36 columns to fill, where every cell in the row represented 15 minutes of work, and of course, i had one more column to upload the name of every client that would take each mechanic.
Basically:
So... The grid got way too big, and way too slow to fill. 😭😭
So that's why i'm asking for help. To be able to see, and fill, this workshop hourly-agenda in a more easy way for the girl that's helping me.
I've learned that i can have an hourly view in a calendar view, but i can't figure out how to set a span of time to each record created, that can be seen in the calendar view to block determined time. I'm thinking maybe a duration field type with a formula or something similar, but i just can't get it right.
To illustrate i've come to this point:
The example in the following picture is wrong, because i need that this view shows that the client's job that can be seen in friday, will actually end at 6pm. (It begins at 3pm)
I hope someone can help please 😭😭😭
