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 eShops] table



• The eHours] table

pNote : I didn’t changed the original week configuration.


Like in W_Vann_Hall’s 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 bShops] 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 yShops] table to the oHours] 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 lHours] table.



• 
  
  {NowOpen} : A Rollup field that follows the {Hours} linked field in this table and that rolls up the hidden {Hours} field in the oHours] table (see below again).
  
  
  This is the very ugly part!



• 
  
  {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.



• 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 oHours] table.
    
    
    They are there to let you know the opening/closing times of a store for today.
  
  
  
  
  
  

1.3. The Hours] table :

bHours] 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 eClose 1]} : If the store closes at mid-day.



• 
  
  {Day of the Week mOpen 2]} : If the store closes at mid-day, this is when it opens again.



• 
  
  {Day of the Week sClose 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 pHours] 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.

cNote : I suggest you to use the « Expanded view » to add a new record.


As, there is nothing you can directly modify on the nShops] table, it will automatically gets you to the yHours] table to enter the opening and closing times, with the + Link to a record from eHours] 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 :

mNote : 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 » HShops] 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 wHours] 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 cShops] 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 a TIMEZONE based date + hours and compares it to NOW().
  
  
  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 eHours] 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 »

3. Let’s talk about the " very ugly " part of the cShops] table

3.1. Tips / Landmarks / Things you need to know :

As I said before, the result of formula in the hidden {Hours} field in the oHours] table, converts the all « military style » numeric hours you put into the cHours] 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 nShops] 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 i 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 :

• Formula field reference – Airtable - Support



• Supported timezones for SET_TIMEZONE – Airtable - Support



• Supported format specifiers for DATETIME_FORMAT – Airtable - Support

Now that you know why almost everything in the DShops] table is TIMEZONE based and why the week is configured that way, we can go a little further again.

3.3. The « less ugly » part in eShops] table : {Today Open 1} ∣ {Today Close 1} ∣ {Today Open 2} ∣ {Today Close 2} :

rNote : 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 :

• 
  
  4 TIMEZONE setups that need to be changed in {Today Open 1}
  
  



• 
  
  3 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)

a...]	


		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.

3.4. The « cutest » part in tShops] table : {Today} :

I’ve added this field into the tShops] 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 dShops] 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 fShops] 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 eShops] 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 !

Here, here!

Nice work @Ptt_Pch and @W_Vann_Hall!

Thank you !

I had a lot of fun playing with the formulas of this base