Opening Hours & Closing Time of “something” : How would you do it? [SOLVED]
So, I’m still busy working on my “Pets” base, trying to refine it adding formulas here, tables there, linking things…
But I’m currently stuck on a “How to” kind of problem :roll_eyes: .
Well, to be clear, I can’t see how to go from “this” to “that” and I thought that maybe I could asked how you would do it.
The current situation is :
On my “Pets” base, I’ve got :
-
One “Veterinarians” table where I put the informations about the veterinarians who follows and have followed my pets.
-
One “Adresses” table which helps me to track the stores where I’m buying the food for my pets, the grooming places, etc… This table is linked to a “Spending” table so I can follow the money I spend on my pets, which pets, etc…
Each table currently have informations about the opening days/hours with the help of 2 fields :
-
One colored multiple select field where each day of opening is a colored option :
{Opening days} -
One long text field where I simply wrote the different opening and closing hours :
{Opening hours}
This is looking like this (the options and opening hours are in French as I’m a French speaking person :winking_face: )
The problem is that I don’t find this very practical :confused: .
Concerning :
-
{Opening days}: The colored multiple select field is great to highlight and know when I can go to vets without fearing to be in front of a closed door when on place. -
{Opening hours}: The fact that we actually can’t format the text, doesn’t help me to see and know with just a glance at the table if the vet is currently open or not :confused: .
The second problem I’ve got with {Opening hours} (whether it’s in the “Veterinarians” or the “Adresses” table) is that each Vet/Store have different Opening/Closing days/time :confused: .
The range for {Opening days} goes from weekdays + saturday to 7 days a week passing by only 3 days a week…
For {Opening hours}, it goes from some hours in the morning to 24 hours a day passing by some hours in the morning and some hours in the evening.
And this is very messy :roll_eyes:
What I would like to do is to find a better way (at least a less messy way) to enter those 2 specific informations in those 2 tables so I can tell quickly if the Vet “X” and/or Store “Y” is open or not.
I thought maybe of creating an “Opening hours/days” table, but as each vet/store have different opening hours/days, I don’t see how it will clarify the situation.
I thought of a formula, but I don’t know where to begin…
I thought I’d do both, but well, this is even more obscure to me than the rest.
Well, in conclusion I’m going in circles with this.
So I’m looking for help and advices :winking_face: .
If you needed to maintain a list of “whatever opens and closes at X hour of Y day of the week and/or weekend”, how would you do it in AirTable ?
(If possible… but I don’t see why not :winking_face: )
Thanks in advance for the help :blush: :grinning:
Ahah :grinning_face_with_smiling_eyes: … Well, quite frankly, I’m glad you kind “left me on my own” concerning the possible mid-day closings :winking_face: … It took me quite some time to got it to work but I succeeded, learned a lot and had a tremendous amount of fun :slightly_smiling_face:
And I really need to say this, but you’re an excellent teacher :thumbs_up: :grinning_face_with_smiling_eyes:
Just in case someone needs at least one example of such a base, I’m gonna try to summarize what I did with your Business Hours Demo Base :winking_face: (specifically concerning the mid-day openings/closings)
I’m still a perfect beginner with AirTable, so there might be things to correct, improve or simply change :winking_face:
My “yesterday solution” to integrate the mid-day closings was a semi-solution, a lead. I slept on it and it got me to this :
In the [Hours] table :
-
I created new Opening and Closing fields so that each day (Day of the week >
{DoW}) could have its own mid-day closing time, if necessary. Which means 4 fields for the schedule of 1 day, instead of 2. -
For example : {DoW Open1} | {DoW Close2}
-
Now looks like : {DoW Open1} | {DoW Close1} | {DoW Open2} | {DoW Close2}
Then, still in the [Hours] table, I modified the formula of hidden {Hours} field to include those new 7 pairs of fields, effectively giving me, as a result, something like this :
Then I got back to work on the [Shops] table and created there, 2 new rollup fields ({TodayClose1} and {TodayOpen2}), for the added mid-day closing/opening hours, still linked to the {Shops::Hours} field, and still rolling up the {Hours::Hours} field.
Like you said, now having 28 open and or closing times in the {Hours::Hours}, the characters offset needed to changed, so I did that too :grinning_face_with_smiling_eyes: .
It took me some time, and I don’t know why, to correctly got these new rollup fields to work :thinking: , but at some point, it worked :grinning_face_with_smiling_eyes: .
For the now {TodayOpen1}, the original offset got from :
to 20+1… then 20+6 for {TodayClose1}, 20+11 for {TodayOpen2} and 20+16 {TodayClose2}.
After that, I finally got to the {Shops::NowOpen} field where I used your adapted formula and simply changed the TIMEZONE and the offset :winking_face:
Still set to your TIMEZONE, and if the copy/paste of the formula didn’t damage it, it looks like this :
IF(
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+1,
4
)='0000',
'❌ - Closed',
IF(
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'
),
'YYYY-MM-DD'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),
'YYYY-MM-DD 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'
),
'YYYY-MM-DD'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+6,
4
),
'YYYY-MM-DD HHmm'
)
)
),
'✅ - Open',
IF(
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'
),
'YYYY-MM-DD'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+11,
4
),
'YYYY-MM-DD 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'
),
'YYYY-MM-DD'
)&' '&
MID(
values&'',
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'd'
)
)*20+16,
4
),
'YYYY-MM-DD HHmm'
)
)
),
'✅ - Open',
'❌ - Closed'
)
)
)
And it seems to work perfectly :grinning_face_with_smiling_eyes: !
I just changed a little bit the formatting of the rollups in [Shops] and added a {Today} field for the date… But this will probably evolve in the future, as I’ll continue to learn to use AirTable and write my own formulas :winking_face:
And this is currently looking like this :
And explorable (I think) here :
🕗 Business Hours | Demo | 2 - Airtable
Explore the "🕗 Business Hours | Demo | 2" base on Airtable.
I would just add some remarques concerning this kind of “final first draft” :
- The base is still set in my own
TIMEZONE:yum: - I didn’t change the original configuration of the week. So, It’s still Sunday to Saturday week where Sunday = 0, and Saturday = 6.
@W_Vann_Hall, I really want to thank you again for all your help, work and all the rest ! :thumbs_up: :slightly_smiling_face: !
