Re: Business Hours ∣ Mid-day Closings and Openings Included
1. The Basics :
Basically, this works like the perfectly working W_Vann_Hall’s solution, with few exceptions though and can be seen/copied here.
1.1. A tiny description :
There are 2 tables in this Base :
- The
[Shops]table - The
[Hours]table
[Note : I didn’t changed the original week configuration.
Like in W_Vann_Hall’s :clock8: Business Hours demo base, the week is configured to go from Sunday to Saturday, where Sunday is day n°0 and Saturday is day n°6. This can be changed, but as it was a detail for me, I didn’t look into that.
I didn’t change the hours configuration either. So this base works on a 24h format. But I think that might be changed too.]
1.2. The [Shops] table :
[Shops] table is a kind of summary table where you can see what is now Open/Close and when are the Opening and Closing times for a store (or whatever), today.
[Note : Due to some Airtable technicalities, almost each field in this table is TIMEZONE based.
You’ll need to keep that in mind if/when consulting the Demo Base (as the results might seem erroneous if you’re not in the same TIMEZONE as the base currently is) and/or, later, to get it to work.
The current Demo Base is TIMEZONE set in 'America/Los_Angeles’, like W_Vann_Hall’s Demo Base is.
(For the list of supported timezones in Airtable, see here)
Now that this is out…]
On the [Shops] table you’ll find 7 fields (+ the primary {Name} field) :
-
{Hours}: This is a one-to-one linked record field (Info, as one store can only have one schedule for one day, that links the[Shops]table to the[Hours]table (see below).
This means that when you change the name of a store in this table, the name of the store is automatically changed in the[Hours]table. -
{NowOpen}: A Rollup field that follows the{Hours}linked field in this table and that rolls up the hidden{Hours}field in the[Hours]table (see below again).
This is the very ugly part! -
{Today}: With a tinyDATETIME_FORMAT()formula, it is just there « just in case » to tell you the date and what day of the week it is. - And there are the 4 last fields :
-
{Today Open 1}∣{Today Close 1}∣{Today Open 2}∣{Today Close 2}which with a different aggregation formula works like the{NowOpen}field.
All 4 of them are Rollup fields that follow the{Hours}linked field in this table and roll up the hidden{Hours}field in the[Hours]table.
They are there to let you know the opening/closing times of a store for today.
-
1.3. The [Hours] table :
[Hours] table is where you can see and modify the opening and closing hours of a store (or whatever).
At first sight, I might say, with all the fields, it’s not an easy looking view.
But, it does the work.
On this table you’ll find 29 fields + 2 hidden fields :
An {Alert} field which is simply there to compare the hours you’re entering for the schedule of one store and let you know if you made a mistake while entering the hours.
If you make an error, it will tell you ❗️⚠️❗️ Erroneous Input ❗️⚠️❗️
It’s a great add-on that was in the original solution.
I modified the formula so it still works and you won’t, normally need to look there.
Then, there are the 28 following {Number} type of fields.
They represent the week where each day is divided in 4 fields :
-
{Day of the Week [Open 1]}: For when the store opens. -
{Day of the Week [Close 1]}: If the store closes at mid-day. -
{Day of the Week [Open 2]}: If the store closes at mid-day, this is when it opens again. -
{Day of the Week [Close 2]}: When the store closes at the end of the day.
And then, there are 2 hidden fields :
-
{Hours}: This is the field used by the Rollup fields in the[Shops]table.
I adapted it so, normally again, you won’t need to go there. -
{Shops}: This is the « second part » of the{Hours}linked field in the[Shops]table.
It is used, I think, to create the one-to-one link in the primary{Name}field of the[Hours]table.
I must admit that I didn’t need to change it so, I simply didn’t look at it.
2. Now, how does all that works ?
That’s the question…
2.1. The easy part : The [Hours] table :
Ok, before getting to the « hard stuff », modifying things, etc…, let’s say you now want to add a record to this base, to test it…
Like you do in your other bases, you click on the + button in the [Shops] table.
[Note : I suggest you to use the « Expanded view » to add a new record.
As, there is nothing you can directly modify on the [Shops] table, it will automatically gets you to the [Hours] table to enter the opening and closing times, with the + Link to a record from [Hours] which, after the name of the new record, is the first thing you see on that window.]
This will take you there :
Then, after naming your new record (I chose « Store 2 » in this example) it will look like this :
Now after clicking on this :
You’ll be in front of this :
Go there :
Then search for your new record :
Click on :
And this will lead you to this :
[Note : At that point, the only thing you need to know is that when you add some opening/closing hours, you don’t add them as real hours.]
Like this :
As I said above, the fields where you put your opening/closing hours are {Number} fields, so you need to add them « military style » , like this :
Why ?
Well, simply put, this is for the hidden {Hours} field.
And because everything on the « summary » [Shops] table depends on this hidden field.
This is meant to walk around some Airtable limitations to get to the desired final result.
Technically, what the {Hours} field does is take all the « military style » hours you enter in this table and puts them into a big long string of 140 characters.
The result partially looks like this :
… ∣0830∣1200∣1330∣1800∣…
If an opening/closing time is left blank, the formula automatically replace the blank by 4 zeros, like in this case :
… ∣0600∣0000∣000∣2300∣…
And this string of 140 characters is later used is the formulas of the different Rollup fields [Shops] table.
Normally once again, you won’t need to look at this field.
The formula has been modified and works.
[Note : I’m just saying all this so you can understand what I did in the formulas of the fields in the [Shops] table. So, if you need to change something, you’ll know where to look and what needs to be modified.]
Ok, now that you’ve added some opening/closing times into the [Hours] table, let’s go back to the [Shops] table and see what happened.
2.2. A quick look at the [Shops] table :
[Don’t forget : It is TIMEZONE based]
Technically again, with you’re newly added opening/closing hours, what happens in the [Shops] table is :
- The aggregation formula in the
{NowOpen}field, via the formula of the{Hours}field in the[Hours], translate the « military style » hours you’ve added into aTIMEZONEbased date + hours and compares it toNOW().
The{NowOpen}field then, with sometimes only a few minutes delay, tells you directly if a shop is currently open or not.
(Isn’t that great ?)
And :
- Following differently the same path, the 4 following fields
{Today Open 1}∣{Today Close 1}∣{Today Open 2}∣{Today Close 2}let you know, if a store is closed, if and when it will open again today by translating too the the « military style » hours, still via the formula of the{Hours}field in the[Hours]table, which are now a simple string of characters, into another double string of characters separated by:so it actually looks like a real hour (e.g.: « 18:00 »).
This is it, for the basics!
But, because you’ll need to change the TIMEZONE of the base to adapted it to your own TIMEZONE (if you want to use it/implement it in one of your base), I sadly needs to go further and tell you about the « very ugly part »
