Help

Re: Business Hours ∣ Mid-day Closings and Openings Included

3873 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ptt_Pch
8 - Airtable Astronomer
8 - Airtable Astronomer

An intro :

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 is a step-by-step user guide. Trying to be as clear as I can and because I don’t have the talent of W_Vann_Hall to explain those things, I’ll then repeat myself a lot (sorry :confused: ).
  • I’m a big beginner in writing Airtable formulas, so there might be other and better ways to get to that same result.
    If you got one, don’t hesitate to share :winking_face: .
  • It is not an easy solution and if some part of the one W_Vann_Hall wrote could be considered as " ugly ", well, this is worse :yum: .
    But once you get your nose into it, understanding how it works, it’s not as complicated as it might seems :winking_face: .
  • Because of some " ugliness " of at least one formula in this demo base, if you want to adapt it to your needs/liking, I suggest you to use an external Text Editor with a function " Search & Replace ", it will save you some time :winking_face: .
  • When copying/pasting formulas, be careful to not loose a bracket or a comma.
  • English isn’t my mother language, sorry for my mistakes :blush: .

This step-by-step user guide will go as follow :winking_face: :

1. The Basics

  1. A tiny description
  2. The [Shops] table
  3. The [Hours] table

2. Now, how does all that works ?

  1. The easy part : The [Hours] table
  2. A quick look at the [Shops] table

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

  1. Tips / Landmarks / Things you need to know
  2. The " very ugly " part : the {NowOpen} formula
  3. The " less ugly " part in [Shops] table : {Today Open 1}{Today Close 1}{Today Open 2}{Today...
  4. The " cutest " part in [Shops] table : {Today}

And just in case you want to skip ahead all the explanations, I tried

4. To kind of summarize all this

  1. What you really need to know
  2. What you really need to do

Now, " Into the Further we go " :winking_face: …

9 Replies 9
Ptt_Pch
8 - Airtable Astronomer
8 - Airtable Astronomer

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 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 [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 :
Hidden

  • {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 :
Screen1.png

Then, after naming your new record (I chose « Store 2 » in this example) it will look like this :
Screen2

Now after clicking on this :
Screen3

You’ll be in front of this :
Screen4

Go there :
Existing

Then search for your new record :
Screen5.png

Click on :
Screen6

And this will lead you to this :
Screen7.png


[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 :
Screen8.png

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 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 [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 »

3. Let’s talk about the " very ugly " part of the [Shops] 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 [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.

3.2. The « very ugly » part : the {NowOpen} formula :

It is there to :

  1. Extract the hours you’ve entered in the [Hours] table (via the {Hours} field).
  2. Determine when NOW() is.
  3. Compare the hours you’ve entered with NOW().
  4. Tell you if a store is currently (with, sometimes, a tiny ridiculous delay though) [🔵 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.

=> 1st case scenario :

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)

=> 2nd case scenario :

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)

=> 3rd case scenario :

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:

  • 8h30 to 12h
  • 13h30 to 18h
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)

=> 4th case scenario :

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)

=> 5th case scenario :

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)

=> 6th case scenario :

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)

=> In any other cases :

[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 …

Ptt_Pch
8 - Airtable Astronomer
8 - Airtable Astronomer

3.3. The « less ugly » part in [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 :

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

[...]	
		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 [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: !

Ptt_Pch
8 - Airtable Astronomer
8 - Airtable Astronomer

4. To kind of summarize all this :

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).

4.1 What you really need to know :

On the [Hours] table :

  • The week is configured as follow :

    • Sunday => Saturday
    • Sunday = Day n°0
    • Saturday = Day n°6
      • This might be changed to your liking
  • 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.
      • This means that : 1 week = 28 {Numeric} fields
  • There are 3 fields you won’t normally need to look at :

    • {Alert}
    • 2 hidden fields : {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.:

  • 8h30 = 830
  • 18h = 1800

On the [Shops] table :

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

    • => The store = [ :large_blue_diamond: Open All Day]
  • If {Day of the Week [Open 1]} + {Day of the Week [Close 2]} = 0000 (or left empty)

    • => The store = [ :large_orange_diamond: Closed All Day]
      (Normally, in this case, the 4 fields are 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}

4.2. What you really need to do :

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 adapt

To 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)
					)	
				)

Whatever you’ll decide to do with all this :

I’m sorry this was so long :blush: but thanks for reading :slightly_smiling_face: !!!


[The End… Finally :yum: ]

Here, here!

Nice work @Ptt_Pch and @W_Vann_Hall!

Thank you :slightly_smiling_face: !

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