Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 06, 2018 10:10 AM
A few days ago, working on one of my bases (dedicated to my pets), I requested some help to know if there was a way to track down, within Airtable, the Opening and Closing times of stores (or whatever opens and closes at some point), as the {Date}
fields and {Duration}
fields actually don’t allow an easy solution :thinking: .
Well, fortunately, W_Vann_Hall gave me a nice perfectly working solution for which I’m still very thankful :slightly_smiling_face: :thumbs_up: , put everything in a demo base Airtable - :clock8: Business Hours demo so I could see how it looked and gave me all the explanations so I could understand how it works.
I was well served! :winking_face:
The only tiny problem was that I needed to track down mid-day closing and opening times too :face_without_mouth: .
(From where I am, there are lots of stores that close at mid-day)
It took me quite a while, a lot of thinking :thinking: and testing and a lot of coffee :coffee: , but today, I finally can say that I succeeded :grinning_face_with_smiling_eyes: .
So, now that I’m finished with it, that it is ready to be implemented in my “Pets base” and while everything is still fresh in my mind :yum: , I thought that, maybe, it could interest someone here, in the future (who knows?) :winking_face: .
But, before getting into the thick of it, I need to make some remarks :
This step-by-step user guide will go as follow :winking_face: :
[Shops]
table[Hours]
table2. Now, how does all that works ?
[Hours]
table[Shops]
table3. Let’s talk about the " very ugly " part of the [Shops]
table
{NowOpen}
formula[Shops]
table : {Today Open 1}
∣ {Today Close 1}
∣ {Today Open 2}
∣ {Today...
[Shops]
table : {Today}
And just in case you want to skip ahead all the explanations, I tried…
4. To kind of summarize all this
Now, " Into the Further we go " :winking_face: …
May 06, 2018 10:14 AM
Basically, this works like the perfectly working W_Vann_Hall’s solution, with few exceptions though and can be seen/copied here.
There are 2 tables in this Base :
[Shops]
table[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.]
[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).[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).{Today}
: With a tiny DATETIME_FORMAT()
formula, it is just there « just in case » to tell you the date and what day of the week it is.{Today Open 1}
∣ {Today Close 1}
∣ {Today Open 2}
∣ {Today Close 2}
which with a different aggregation formula works like the {NowOpen}
field.{Hours}
linked field in this table and roll up the hidden {Hours}
field in 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.{Shops}
: This is the « second part » of the {Hours}
linked field in the [Shops]
table.{Name}
field of the [Hours]
table.May 06, 2018 10:21 AM
That’s the question…
[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.
[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 :
{NowOpen}
field, via the formula of the {Hours}
field in the [Hours]
, translate the « military style » hours you’ve added into a TIMEZONE
based date + hours and compares it to NOW()
.{NowOpen}
field then, with sometimes only a few minutes delay, tells you directly if a shop is currently open or not.And :
{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 »
May 06, 2018 10:37 AM
[Shops]
tableAs I said before, the result of formula in the hidden {Hours}
field in the [Hours]
table, converts the all « military style » numeric hours you put into the [Hours]
table into a big long string of 140 characters.
This means that, for the formulas in the [Shops]
table to work, the formulas need to know what to look for in this big long string and where to begin to look.
As a complete week schedule for a store is now looking like this :
(e.g. : the weekly opening/closing schedule for « Store 2 »)
2400|0000|0000|0000|
0000|0000|0000|0000|
0830|1200|1330|1800|
0730|1200|0000|0000|
0000|0000|1300|2300|
0600|0000|0000|2300|
0830|1200|1330|1800|
The first row is for the Sunday opening
| mid-day closing
| mid-day opening
| closing
times and the last one, is for the Saturday.
To know where to look at, a simple offset of characters has to determined and extracted with a MID()
text function.
On the Formula field reference – Airtable - Support page, you’ll find this concerning the MID()
text function :
MID(string, whereToStart, count)
Extract a substring of count characters starting at whereToStart.
MID("quick brown fox", 6, 5)
=> brown
As I spent quite some time on this and to simplify the explanations that will follow, I can tell you that in the formulas used in the different {fields}
in the [Shops]
table, the whereToStar
in the MID()
function corresponds to :
For the 1st « column » above is : 20+1
=> And this corresponds to {Today Open 1}
For the 2nd « column » above is : 20+6
=> And this corresponds to {Today Close 1}
For the 3rd « column » above is : 20+11
=> And this corresponds to {Today Open 2}
For the 4th « column » above is : 20+16
=> And this corresponds to {Today Close 2}
The count of characters in the MID()
function is always : 4
As everything is TIMEZONE
based and compared to NOW()
and because a string of textual characters has to be converted into a numeric value to be compared, in the MID()
function the string
has to be determined with a VALUE()
function .
On the Formula field reference – Airtable - Support page, you’ll find this concerning the VALUE()
numeric function :
VALUE(text)
Converts the text string to a number.
VALUE("$1000")
=> 1000
In this case, the text
corresponds to « when is NOW()
» in association with the characters offset, so later, this part can be compared with « Today’s NOW()
» therefore determining if a store is currently [🔵 Open]
or [🔴 Closed]
in the {NowOpen}
formula.
And this is done with a DATETIME_FORMAT()
function including a SET_TIMEZONE()
function expressed in ’d'
which is following the list of Supported format specifiers for DATETIME_FORMAT – Airtable - Support
d
Day of the week, cardinal nos. 0-6
=> 0 1 … 5 6.
(A Sunday => Saturday week where Sunday = 0 and Saturday = 6)
Put all of that together and you always got :
{Today Open 1}
=
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
{Today Close 1}
=
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
{Today Open 2}
=
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
{Today Close 2}
=
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
Once you know all that, there is not much left to understand.
Just in case, I leave you the different Airtable - Support links you can consult to help you go through all this here :
Now that you know why almost everything in the [Shops]
table is TIMEZONE
based and why the week is configured that way, we can go a little further again.
May 06, 2018 10:41 AM
{NowOpen}
formula :It is there to :
[Hours]
table (via the {Hours}
field).NOW()
is.NOW()
.[🔵 Open]
or [🔴 Closed]
This is its sole purpose.
But, to include the mid-day opening/closing times of a store, I had to re-think entirely how {NowOpen}
, in its original form was working.
And I had to make choices and specify when a store could be considered by the {NowOpen}
formula as [🔵 Open]
or [🔴 Closed]
.
This lead me to a very big long IF()
and some other IF()
, AND()
and OR()
in-between to determine what needs to be done in each of the 6 possible case scenarios (=> 6 IF()
).
I sincerely didn’t want to go there, but the fact is that I didn’t find any other way to make this work.
In its entirety, and in words, the actual {NowOpen}
formula now works like this :
IF(
[Today Open 1] = '2400',
THEN
[Shop] is [🔷 Open All Day],
(Otherwise)
IF(
AND(
[Today Open 1] = '0000',
[Today Close 2] = '0000'
),
THEN
[Shop] is [🔶 Closed All Day],
(Otherwise)
IF(
AND(
[Today Open 1] != '0000',
[Today Close 1] != '0000',
[Today Open 2] != '0000',
[Today Close 2] != '0000',
OR(
AND(
[Today Open 1] IS_BEFORE(NOW()),
[Today Close 1] IS_AFTER(NOW())
),
AND(
[Today Open 2] IS_BEFORE(NOW()),
[Today Close 2] IS_AFTER(NOW())
)
)
),
THEN
[Shop] is [🔵 Open],
(Otherwise)
IF(
AND(
[Today Open 1] != '0000',
[Today Close 1] != '0000',
[Today Open 2] = '0000',
[Today Close 2] = '0000',
[Today Open 1] IS_BEFORE(NOW()),
[Today Close 1] IS_AFTER(NOW()),
),
THEN
[Shop] is [🔵 Open],
(Otherwise)
IF(
AND(
[Today Open 1] = '0000',
[Today Close 1] = '0000',
[Today Open 2] != '0000',
[Today Close 2] != '0000',
[Today Open 2] IS_BEFORE(NOW()),
[Today Close 2] IS_AFTER(NOW()),
),
THEN
[Shop] is [🔵 Open],
(Otherwise)
IF(
AND(
[Today Open 1] != '0000',
[Today Close 1] = '0000',
[Today Open 2] = '0000',
[Today Close 2] != '0000',
[Today Open 1] IS_BEFORE(NOW()),
[Today Close 2] IS_AFTER(NOW()),
),
THEN
[Shop] is [🔵 Open],
(Otherwise)
[Shop] is [🔴 Closed]
)
)
)
)
)
)
Just in case and because the definitive{NowOpen}
formula looks very bad at first sight, I’m gonna explore this with you.
For a store that doesn’t close that day and therefore is open 24h/24
The {NowOpen}
formula gives you this result when, in the [Hours]
table, the corresponding {Day of the Week [Open 1]} = 2400
IF(
[Today Open 1] = '2400',
THEN
[Shop] is [🔷 Open All Day],
(Otherwise)
For a store that doesn’t open that day.
The {NowOpen}
formula gives you this result when, in the [Hours]
table, the corresponding {Day of the Week [Open 1]} = 0000
and {Day of the Week [Close 2]} = 0000
.
Well, in fact, as the store doesn’t open, the 4 dedicated fields corresponding to that day will be left empty.
IF(
AND(
[Today Open 1] = '0000',
[Today Close 2] = '0000'
),
THEN
[Shop] is [🔶 Closed All Day],
(Otherwise)
For a store that opens in the morning, closes at mid-day and then re-opens in the afternoon before closing for the day.
The {NowOpen}
formula gives you this result when, in the [Hours]
table, the 4 corresponding fields ( => {Day of the Week [Open 1]}
∣ {Day of the Week [Close 1]}
∣ {Day of the Week [Open 2]}
∣ {Day of the Week [Close 2]}
) are completed.
It is meant to be used for a store with this kind of schedule:
IF(
AND(
[Today Open 1] != '0000',
[Today Close 1] != '0000',
[Today Open 2] != '0000',
[Today Close 2] != '0000',
OR(
AND(
[Today Open 1] IS_BEFORE(NOW()),
[Today Close 1] IS_AFTER(NOW())
),
AND(
[Today Open 2] IS_BEFORE(NOW()),
[Today Close 2] IS_AFTER(NOW())
)
)
),
THEN
[Shop] is [🔵 Open],
Technically, because in this case, the store can’t be open in the morning and in the afternoon at the same time, this is saying that :
IF()
the 4 fields corresponding to that day in the [Hours]
table are not empty, AND()
, IF()
NOW()
is between the corresponding {Day of the Week [Open 1]}
and the corresponding {Day of the Week [Close 1]}
OR()
IF()
NOW()
is between the corresponding {Day of the Week [Open 2]}
and the corresponding {Day of the Week [Close 2]}
, then [Shop] is [🔵 Open]
.
(Otherwise)
For a store that opens only in the morning.
The {NowOpen} formula gives you this result when, in the [Hours]
table, only when the 2 corresponding {Day of the Week [Open 1]}
and {Day of the Week [Close 1]}
are completed and the other 2 left empty.
IF(
AND(
[Today Open 1] != '0000',
[Today Close 1] != '0000',
[Today Open 2] = '0000',
[Today Close 2] = '0000',
[Today Open 1] IS_BEFORE(NOW()),
[Today Close 1] IS_AFTER(NOW()),
),
THEN
[Shop] is [🔵 Open],
This reads :
IF()
the 2 fields corresponding to {Day of the Week [Open 1]}
and the corresponding {Day of the Week [Close 1]}
in the [Hours]
table are not empty, AND()
, IF()
NOW()
is between {Day of the Week [Open 1]}
and {Day of the Week [Close 1]}
, then [Shop] is [🔵 Open]
.
(Otherwise)
For a store that opens only in the afternoon / evening.
The {NowOpen}
formula gives you this result when, in the [Hours]
table, only when the 2 corresponding {Day of the Week [Open 2]}
and {Day of the Week [Close 2]}
are completed and the other 2 left empty.
IF(
AND(
[Today Open 1] = '0000',
[Today Close 1] = '0000',
[Today Open 2] != '0000',
[Today Close 2] != '0000',
[Today Open 2] IS_BEFORE(NOW()),
[Today Close 2] IS_AFTER(NOW()),
),
THEN
[Shop] is [🔵 Open],
This reads :
IF()
the 2 fields corresponding to {Day of the Week [Open 2]}
and the corresponding {Day of the Week [Close 2]}
in the [Hours]
table are not empty, AND()
, IF()
NOW()
is between {Day of the Week [Open 2]}
and {Day of the Week [Close 2]}
, then [Shop] is [🔵 Open]
.
(Otherwise)
For a store that opens and closes but without mid-day closing / opening.
The {NowOpen}
formula gives you this result when, in the [Hours]
table, only when the 2 corresponding {Day of the Week [Open 1]}
and {Day of the Week [Close 2]}
are completed and the other 2 left empty.
IF(
AND(
[Today Open 1] != '0000',
[Today Close 1] = '0000',
[Today Open 2] = '0000',
[Today Close 2] != '0000',
[Today Open 1] IS_BEFORE(NOW()),
[Today Close 2] IS_AFTER(NOW()),
),
THEN
[Shop] is [🔵 Open],
This reads :
IF()
the 2 fields corresponding to {Day of the Week [Open 1]}
and the corresponding {Day of the Week [Close 2]}
in the [Hours]
table are not empty, AND()
, IF()
NOW()
is between {Day of the Week [Open 1]}
and {Day of the Week [Close 2]}
, then [Shop] is [🔵 Open]
.
(Otherwise)
[Shop] is [🔴 Closed]
It didn’t make sense to me to add a « mid-day opening only ».
In the demo base, the first 6 records I created as examples follow that order.
The last one is the one I created for the need of this « step-by-step » user guide.
Now, the {NowOpen}
formula where you’ll need to adapt the 49 TIMEZONE
setup is this one :
[Note 1 : what you need to search exactly is America/Los_Angeles
and you need to replace it by YourPartOfTheWorld/YourTimezone
Your will find YourTimzone
there => Supported timezones for SET_TIMEZONE – Airtable - Support.]
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='2400',
'🔷 [Open All Day]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)='0000'
),
'🔶 [Closed All Day]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)!='0000',
OR(
AND(
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),
'DD/MM/YYYY HHmm'
)
)
),
AND(
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),
'DD/MM/YYYY HHmm'
)
)
)
)
),
'🔵 [Open]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)='0000',
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),
'DD/MM/YYYY HHmm'
)
)
),
'🔵 [Open]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)!='0000',
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),
'DD/MM/YYYY HHmm'
)
)
),
'🔵 [Open]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)!='0000',
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),
'DD/MM/YYYY HHmm'
)
)
),
'🔵 [Open]',
'🔴 [Closed]'
)
)
)
)
)
)
[Note 2 : There is, in the formula above, a part that repeats itself relentlessly and that looks like this :
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
)
This part is needed to compare NOW()
to the different opening/closing times fields and then determine if NOW()
is before or after those.
As this didn’t change it and as I can’t explain this better than W_Vann_Wall did in his original answer to me, I’m simply gonna guide you to his reply.
That was for the « very ugly » part.
But it doesn’t end there …
May 06, 2018 10:42 AM
[Shops]
table : {Today Open 1}
∣ {Today Close 1}
∣ {Today Open 2}
∣ {Today Close 2}
:[Note : Like for the {NowOpen}
formula you’ll need to adapt the TIMEZONE
setup in those 4 {fields}
:
And again, what you need to search exactly is America/Los_Angeles
and you need to replace it by YourPartOfTheWorld/YourTimezone
Your will find YourTimzone
there => Supported timezones for SET_TIMEZONE – Airtable - Support.
There are :
TIMEZONE
setups that need to be changed in {Today Open 1}
TIMEZONE
setups that need to be changed in {Today Close 1}
∣ {Today Open 2}
and {Today Close 2}
]Because the « hours » that those 4 fields show you are coming from a string of text, to format them, I had to write a tiny formula that kind of looks like this :
E.g.: for {Today Open 1}
:
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='0000',
BLANK(),
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='2400',
BLANK(),
CONCATENATE(
LEFT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),2),
":",
RIGHT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),2)
)
)
)
What this does is :
IF(
{Today Open 1} = '0000'
Then
BLANK(),
(Otherwise)
IF(
{Today Open 1} = '2400'
Then
BLANK(),
[...]
Put differently :
If the store is currently Close or If the store is always « Open », leave the field empty.
(Otherwise)
[...]
CONCATENATE(
LEFT(
{Today Open 1}
),2),
":",
RIGHT(
{Today Open 1}
),2)
)
)
Put differently :
If it is not the case, write the 2 first characters (LEFT()
) included in the string composing {Today Open 1}
, separate them by ":"
and then write the 2 last characters (RIGHT()
) included in the string composing {Today Open 1}
.
The 3 other fields {Today Close 1}
∣ {Today Open 2}
∣ {Today Close 2}
simply follow the same pattern except for the fact that there is no other ='2400'
.
And this gives you a string of 4 characters that now looks like a real hour.
This, can be changed at your liking.
At some point, I made version of this base where {Today Open 1}
and {Today Close 1}
appeared , with the date, in the same Rollup field (kind of like it was at the origin) but I found this not very easy to read.
So I choose to leave it that way.
[Shops]
table : {Today}
:I’ve added this field into the [Shops]
table because, in the solution W_Vann_Hall gave me, today’s date was included in the Rollup fields displaying the opening/closing times for a store for today (see just above).
I’ve found that is was actually great and useful to see, on the [Shops]
table, what « today » was, so I wanted to keep it.
I’ve tried to include today’s date in {Today Open 1}
but, once more, for a question of readability of the [Shops]
table, I did prefer to keep it separated.
So, I just wrote a tiny TIMEZONE
based (again) formula that just do that : Display the day of the week and today’s date.
The TIMEZONE
is not a necessity. It is just there because everything else is TIMEZONE
based. I just thought that because it is separated from the rest it could lead to some possible errors.
It is simply meant to guarantee that each and every result you read on the [Shops]
table follows the same path, thus the same TIMEZONE
.
Fortunately, there is just 1 TIMEZONE
to adapt in {Today}
formula which is :
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ddd ∣ DD/MM/YYYY'
)
The result will very simply looks something like this : e.g.: for today :Sun ∣ 06/05/2018
This too can be changed at your liking.
And this is finally it :slightly_smiling_face: !
May 06, 2018 10:45 AM
Just in case you don’t need detailed explanations and because the demo base actually works in this actual form (at least for me, after many different tests).
[Hours]
table :The week is configured as follow :
1 day of the week is divided in 4 {Numeric}
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.
There are 3 fields you won’t normally need to look at :
{Alert}
{Hours}
and {Shops}
To enter the opening/closing times and mid-day closing/opening times if necessary, you need to enter them in the appropriate fields « military style » in a 24h format.
E.g.:
Almost every field on this table is TIMEZONE
based.
The TIMEZONE
used in the Demo Base is America/Los_Angeles
=> The formulas of the fields in this table needs to be adapted to YourTimezone
.
The {NowOpen}
field, depending on what you’ve entered as « hours » for a store in the [Hours]
table will give you the current opening/closing status of a store.
But there are 2 specific cases :
If {Day of the Week [Open 1]}
= 2400
If {Day of the Week [Open 1]}
+ {Day of the Week [Close 2]}
= 0000 (or left empty)
In any other cases, following the schedule for one store you’ve entered for one day, the store will be marked as [ :large_blue_circle: Open] or [ :red_circle: Closed].
On the [Shops]
table :
{Day of the Week [Open 1]}
: For when the store opens.{Today Open 1}
{Day of the Week [Close 1]}
: If the store closes at mid-day.{Today Open 1}
{Day of the Week [Open 2]}
: If the store closes at mid-day, this is when it opens again.{Today Open 1}
{Day of the Week [Close 2]}
: When the store closes at the end of the day.{Today Open 1}
Besides, when adding a record in the [Shops]
table, adding the corresponding « hours » in the [Hours]
table « military style » is :
=> To change the TIMEZONE
found in the formulas of the different fields in the [Shops]
table and adapt them to YourTimezone
.
The concerned fields are :
{NowOpen}
: There are 49 TIMEZONE
setups to adapt (sorry){Today}
: 1 TIMEZONE
setup to adapt{Today Open 1}
: 4 TIMEZONE
setups to adapt{Today Close 1}
: 3 TIMEZONE
setups to adapt{Today Open 2}
: 3 TIMEZONE
setups to adapt{Today Close 2}
: 3 TIMEZONE
setups to adaptTo do this :
You’ll need to search exactly for America/Los_Angeles
in the formulas and you’ll need to replace it by YourPartOfTheWorld/YourTimezone
.
Your will find YourTimzone
there => Supported timezones for SET_TIMEZONE – Airtable - Support.
The concerned formulas are :
{NowOpen}
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='2400',
'🔷 [Open All Day]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)='0000'
),
'🔶 [Closed All Day]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)!='0000',
OR(
AND(
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),
'DD/MM/YYYY HHmm'
)
)
),
AND(
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),
'DD/MM/YYYY HHmm'
)
)
)
)
),
'🔵 [Open]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)='0000',
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),
'DD/MM/YYYY HHmm'
)
)
),
'🔵 [Open]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)!='0000',
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),
'DD/MM/YYYY HHmm'
)
)
),
'🔵 [Open]',
IF(
AND(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)!='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)='0000',
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)!='0000',
IS_AFTER(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),
'DD/MM/YYYY HHmm'
)
),
IS_BEFORE(
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'DD/MM/YYYY'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),
'DD/MM/YYYY HHmm'
)
)
),
'🔵 [Open]',
'🔴 [Closed]'
)
)
)
)
)
)
{Today}
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ddd ∣ DD/MM/YYYY'
)
{Today Open 1}
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='0000',
BLANK(),
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='2400',
BLANK(),
CONCATENATE(
LEFT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),2),
":",
RIGHT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
),2)
)
)
)
{Today Close 1}
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
)='0000',
BLANK(),
CONCATENATE(
LEFT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),2),
":",
RIGHT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),2)
)
)
{Today Open 2}
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
)='0000',
BLANK(),
CONCATENATE(
LEFT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
),2),
":",
RIGHT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
),2)
)
)
And finally :
{Today Close 2}
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
)='0000',
BLANK(),
CONCATENATE(
LEFT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),2),
":",
RIGHT(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),2)
)
)
May 06, 2018 10:47 AM
Whatever you’ll decide to do with all this :
[The End… Finally :yum: ]
May 06, 2018 12:46 PM
Here, here!
Nice work @Ptt_Pch and @W_Vann_Hall!
May 06, 2018 03:20 PM
Thank you :slightly_smiling_face: !
I had a lot of fun playing with the formulas of this base :slightly_smiling_face: