Unique dates from range (start date - end date)

SMRK · May 4, 2018, 8:24am

I need help with a date calculation. In a table I have two fields, one with a ‘start date’ and the other one with an ‘end date’. Now I need a third field that gives me all the dates that fall into this timespan including start and end date.

For example my start date is May 5th and my end date is May 10th. In the range field I want to have ’ May 5th, May 6th, May 7th, May 8th, May 9th, May 10th’.

Going on from there I would need to make a lookup or rollup in an other table to combine all those days from different records belonging to an item. I’m trying to achieve a rental and availability table for our equipment and need people to see at what days something is out of our storage. I wish there was a multiple date select field that would make this so much easier.

I’m sure the Airtable veterans have a solution for my problem.

Thank you very much for your help!!!

Jeremy_Oglesby · May 4, 2018, 5:05pm

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.

SMRK · May 4, 2018, 5:15pm

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.

Jeremy_Oglesby · May 4, 2018, 5:20pm

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.

SMRK · May 4, 2018, 5:28pm

Yup, that’s an idea along the lines of what I was trying but I couldn’t wrap my head around it yet.

W_Vann_Hall · May 4, 2018, 9:56pm

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. (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, [Table 1]. The first three fields in [Table 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.)

Jeremy_Oglesby · May 4, 2018, 10:03pm

SMRK · May 5, 2018, 5:24am

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.

W_Vann_Hall · May 6, 2018, 3:11am

[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. ) 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, [Equipment] and [Rentals]. In [Equipment], 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 [Rentals] table to display a list of existing rentals
selects '+ Add new record', creating a new [Rentals] 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 [Rentals] 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 [Rentals] 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 [Rentals]’ 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.

SMRK · May 7, 2018, 6:30am

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?

W_Vann_Hall · May 7, 2018, 8:00am

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.

W_Vann_Hall · May 18, 2018, 2:40am

Mark -

I have a couple of questions to ask about your application. Could you message me here or at wvannhall[at]paladesigns[dot]com with a way to reach you?

Thanks!

Eduard_Ichim · June 5, 2019, 2:01pm

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.

W_Vann_Hall · June 6, 2019, 1:49am

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 [To 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 [Table 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 [Table 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.

Walid_Chaker · November 3, 2019, 5:53pm

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

system · April 27, 2020, 10:03pm

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.