Unique dates from range (start date - end date)

[SMRK]

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!!!

[W_Vann_Hall]

SMRK:

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.

[W_Vann_Hall]

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]

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]

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. :winking_face:

[Walid_Chaker]

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

[Pao_R]

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 😭😭😭