Skip to main content

Opening Hours & Closing Time of “something” : How would you do it? [SOLVED]

P

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


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




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


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 😊 😀

    8 replies

    W
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • April 24, 2018

    The way I would do it is a little ugly beneath the hood — OK, it’s very ugly, thanks to the need to ensure Airtable is using the correct timezone — but it does give you what you want.


    Note: In my demonstration base, I have implemented my solution with absolutely no regard for your existing base, and it ignores or eliminates some of your nice features. If you accept the need for a little redundancy, both your and my solutions can be made to coexist — but I’ll leave that exercise for extra credit.


    Also note: I’ve left days of the week in English, since I’m a boorish American and we just assume everybody else speaks English. :winking_face: [1]


    A base demonstrating my approach can be found here; after opening the base, select ‘Duplicate base’ to copy it into your own workspace; this will allow you to examine, copy, and modify the code.


    In this stripped-down version, the base contains two tables, [Shops], with records indicating pet shops (or vets, groomers, whatever), and [Hours], with records representing weekly opening and closing times. The {Shops::Hours} field (that is, the {Hours} field in the [Shops] table) is a linked record field representing a one-to-one link between [Shops] and [Hours].


    In normal operations, the [Hours] table would be populated as a drill-through from [Shops]. From the [Shops] table, the user selects the plus sign in the {Hours} field, which opens a list of [Hours] records. The user then selects '+ Add new record', which creates a new [Hours] record and opens it for data entry.


    Each [Hours] record contains 7 pairs of fields indicating opening and closing hours for each day of the week. Times should be entered in 'HHMM' format, with no colon separating hours from minutes, based on a 24-hour clock. (As a way of providing minor data validation, I’ve formatted the fields as integers.)


    If a shop is not open on a given day, leave both the opening and closing times blank. (Alternatively they can both be set to zero.)


    Also as a minor check on data integrity, the {Alert} field monitors each pair of values. If the opening time for a day is later than the close time, the field displays '🔥🔥🔥 Erroneous input'.


    (Behind the scenes, the {Hours::Hours} field takes all of the opening and closing times and concatenates them into a single string: '0000|0000|0800|1700|...'. This value is accessed by the [Shops] table and used to determine whether a shop is currently open.)


    Note: I’ve built this using a Sunday through Saturday week, with Sunday equating to Day 0 and Saturday to Day 6. These values may note be correct for you, based on your locale. You may need to adjust as appropriate.


    Once the user has finished entering opening and closing times for the week, he or she closes the [Hours] detail entry window and is returned to the [Shops] table.


    Now here’s the ugly part.


    {Shops::NowOpen} is a rollup field that follows the {Hours} linked record to roll up the {Hours::Hours} field using the following aggregation formula:


    IF(

 MID(

  values&'',

  VALUE(

   DATETIME_FORMAT(

    SET_TIMEZONE(

     NOW(),

     'America/Los_Angeles'

     ),

    'd'

    )

   )*10+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'

        )

       )*10+1,

       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'

        )

       )*10+6,

       4

      ),

      'YYYY-MM-DD HHmm'

     )

    )

   ),

  '✅ - Open',

  '❌ - Closed'

  )

 )

    Ordinarily, I’d say to copy and paste this formula into your base and then forget about it, except you can’t: You’ll need to change the timezone indicator to your local timezone as listed here.


    Essentially, what this formula does is creates an opening and a closing datetime-stamp based on today’s year, month, and date with either the shop’s opening or closing time appended. It then converts these to Airtable datetime-stamps and compares each with the current date and time. If NOW() falls between today’s opening and closing times, it shows the shop is open; otherwise, it shows it is closed.


    The section of the routine that reads


    DATEADD(

  NOW(),

  VALUE(

    DATETIME_FORMAT(

      SET_TIMEZONE(

        NOW(),

        'America/Los_Angeles'

        ),

      'ZZ'

      )

    )/100,

  'hours'

  )

    restates NOW() as UTC time so it may be compared correctly with the output of DATETIME_PARSE(). The ‘ZZ’ format specifier causes DATETIME_FORMAT() to return the difference between local time and UTC as positive or negative hours and minutes, like so: '-0700'. To apply this shift, the formula converts that string to a numeric value and then divides it by 100, eliminating the minutes; the resulting value is then added to NOW() using DATEADD().


    I’ve included two other rollup fields displaying today’s opening and closing times. They can be reformatted as you desire.


    I think this gives you what you were looking for. If you have any trouble folding it into your existing base, please let me know. (And please let me know if it returns an incorrect value; it took a while before I got it to work. In such cases, there is always the chance the formula started working because we reached the proper time-of-day, and not because it’s actually correct.)






    1. Actually, I am probably the worst language student ever: Over the years, I’ve studied Spanish, Sanskrit, French, German, and a smattering of Japanese, and, still, I am literate only in English…

    D
    M
    D
    7 people like this
    • D
      David_Vasandani
    • M
      Michael_Fray
    • D
      Donald_Newlands
    • P
      Ptt_Pch
    • V
      vpego
    • L
      Lewis_Sternberg
    • J
      Jeremy_Oglesby
    P
    • Ptt_Pch
    • Author
    • Known Participant
    • 91 replies
    • April 25, 2018
    W_Vann_Hall wrote:

    The way I would do it is a little ugly beneath the hood — OK, it’s very ugly, thanks to the need to ensure Airtable is using the correct timezone — but it does give you what you want.


    Note: In my demonstration base, I have implemented my solution with absolutely no regard for your existing base, and it ignores or eliminates some of your nice features. If you accept the need for a little redundancy, both your and my solutions can be made to coexist — but I’ll leave that exercise for extra credit.


    Also note: I’ve left days of the week in English, since I’m a boorish American and we just assume everybody else speaks English. :winking_face: [1]


    A base demonstrating my approach can be found here; after opening the base, select ‘Duplicate base’ to copy it into your own workspace; this will allow you to examine, copy, and modify the code.


    In this stripped-down version, the base contains two tables, [Shops], with records indicating pet shops (or vets, groomers, whatever), and [Hours], with records representing weekly opening and closing times. The {Shops::Hours} field (that is, the {Hours} field in the [Shops] table) is a linked record field representing a one-to-one link between [Shops] and [Hours].


    In normal operations, the [Hours] table would be populated as a drill-through from [Shops]. From the [Shops] table, the user selects the plus sign in the {Hours} field, which opens a list of [Hours] records. The user then selects '+ Add new record', which creates a new [Hours] record and opens it for data entry.


    Each [Hours] record contains 7 pairs of fields indicating opening and closing hours for each day of the week. Times should be entered in 'HHMM' format, with no colon separating hours from minutes, based on a 24-hour clock. (As a way of providing minor data validation, I’ve formatted the fields as integers.)


    If a shop is not open on a given day, leave both the opening and closing times blank. (Alternatively they can both be set to zero.)


    Also as a minor check on data integrity, the {Alert} field monitors each pair of values. If the opening time for a day is later than the close time, the field displays '🔥🔥🔥 Erroneous input'.


    (Behind the scenes, the {Hours::Hours} field takes all of the opening and closing times and concatenates them into a single string: '0000|0000|0800|1700|...'. This value is accessed by the [Shops] table and used to determine whether a shop is currently open.)


    Note: I’ve built this using a Sunday through Saturday week, with Sunday equating to Day 0 and Saturday to Day 6. These values may note be correct for you, based on your locale. You may need to adjust as appropriate.


    Once the user has finished entering opening and closing times for the week, he or she closes the [Hours] detail entry window and is returned to the [Shops] table.


    Now here’s the ugly part.


    {Shops::NowOpen} is a rollup field that follows the {Hours} linked record to roll up the {Hours::Hours} field using the following aggregation formula:


    IF(

 MID(

  values&'',

  VALUE(

   DATETIME_FORMAT(

    SET_TIMEZONE(

     NOW(),

     'America/Los_Angeles'

     ),

    'd'

    )

   )*10+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'

        )

       )*10+1,

       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'

        )

       )*10+6,

       4

      ),

      'YYYY-MM-DD HHmm'

     )

    )

   ),

  '✅ - Open',

  '❌ - Closed'

  )

 )

    Ordinarily, I’d say to copy and paste this formula into your base and then forget about it, except you can’t: You’ll need to change the timezone indicator to your local timezone as listed here.


    Essentially, what this formula does is creates an opening and a closing datetime-stamp based on today’s year, month, and date with either the shop’s opening or closing time appended. It then converts these to Airtable datetime-stamps and compares each with the current date and time. If NOW() falls between today’s opening and closing times, it shows the shop is open; otherwise, it shows it is closed.


    The section of the routine that reads


    DATEADD(

  NOW(),

  VALUE(

    DATETIME_FORMAT(

      SET_TIMEZONE(

        NOW(),

        'America/Los_Angeles'

        ),

      'ZZ'

      )

    )/100,

  'hours'

  )

    restates NOW() as UTC time so it may be compared correctly with the output of DATETIME_PARSE(). The ‘ZZ’ format specifier causes DATETIME_FORMAT() to return the difference between local time and UTC as positive or negative hours and minutes, like so: '-0700'. To apply this shift, the formula converts that string to a numeric value and then divides it by 100, eliminating the minutes; the resulting value is then added to NOW() using DATEADD().


    I’ve included two other rollup fields displaying today’s opening and closing times. They can be reformatted as you desire.


    I think this gives you what you were looking for. If you have any trouble folding it into your existing base, please let me know. (And please let me know if it returns an incorrect value; it took a while before I got it to work. In such cases, there is always the chance the formula started working because we reached the proper time-of-day, and not because it’s actually correct.)






    1. Actually, I am probably the worst language student ever: Over the years, I’ve studied Spanish, Sanskrit, French, German, and a smattering of Japanese, and, still, I am literate only in English…


    Waow :thumbs_up: 😀 !

    I wasn’t expecting such a complete answer (so quickly, I might add) 🙂 !



    I’ve thought about that :winking_face: .


    After reading and re-reading your reply and explanations quite a few times, I’m pretty sure that, like you said it so well, both your solution and mine can coexist :winking_face: .

    I really don’t mind some redundancy… In fact, I do prefer having some redundancy in a base and get it to work like I want it to work than no redundancy and only a half-working base/table in my point of view :winking_face: .



    Lol, I’m so used to watch/read in English that I often don’t bother to « do » things in French anymore 😋 … Half of my bases are in plain English and the other half is currently in « Frenglish »… It’s the last tiny detail I correct when I get a base to work :winking_face: .


    As for the « ugly part » well, I don’t find it so ugly :grinning_face_with_smiling_eyes: .

    Let’s say I was prepared for that as I knew, before asking and after searching, here, in the Community support, a lead that I could follow, that my question wasn’t necessarily an easy one :winking_face: .


    Your solution does clarify a lot of things in my own mind concerning this problem and I’m ready to play along :grinning_face_with_smiling_eyes: !


    The sad thing is that IRL, I’ve got a busy schedule for the end of this week so it will take me some days to put my eyes, nose and brain cells into this but I’m pretty certain that you’re right and that it does effectively respond to my need 🙂


    Thank you very much for your answer, help, work, demo base, creativity and explanations 🙂 :thumbs_up: !


    Whatever happens, I’ll get back here, to let you know how it all worked out 🙂 :winking_face:

    D
    J
    2 people like this
    • D
      David_Vasandani
    • J
      Jeremy_Oglesby
    W
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • April 25, 2018
    Ptt_Pch wrote:

    Waow :thumbs_up: 😀 !

    I wasn’t expecting such a complete answer (so quickly, I might add) 🙂 !



    I’ve thought about that :winking_face: .


    After reading and re-reading your reply and explanations quite a few times, I’m pretty sure that, like you said it so well, both your solution and mine can coexist :winking_face: .

    I really don’t mind some redundancy… In fact, I do prefer having some redundancy in a base and get it to work like I want it to work than no redundancy and only a half-working base/table in my point of view :winking_face: .



    Lol, I’m so used to watch/read in English that I often don’t bother to « do » things in French anymore 😋 … Half of my bases are in plain English and the other half is currently in « Frenglish »… It’s the last tiny detail I correct when I get a base to work :winking_face: .


    As for the « ugly part » well, I don’t find it so ugly :grinning_face_with_smiling_eyes: .

    Let’s say I was prepared for that as I knew, before asking and after searching, here, in the Community support, a lead that I could follow, that my question wasn’t necessarily an easy one :winking_face: .


    Your solution does clarify a lot of things in my own mind concerning this problem and I’m ready to play along :grinning_face_with_smiling_eyes: !


    The sad thing is that IRL, I’ve got a busy schedule for the end of this week so it will take me some days to put my eyes, nose and brain cells into this but I’m pretty certain that you’re right and that it does effectively respond to my need 🙂


    Thank you very much for your answer, help, work, demo base, creativity and explanations 🙂 :thumbs_up: !


    Whatever happens, I’ll get back here, to let you know how it all worked out 🙂 :winking_face:


    Glad I could help.


    Actually, I should thank you for posting this request, because I had a lot of fun figuring out how to get it to work. Despite its superficial ugliness, in some ways it’s rather an elegant solution. Something tells me I’ll find other uses for this approach before long…

    D
    M
    P
    3 people like this
    • D
      David_Vasandani
    • M
      Michael_Fray
    • P
      Ptt_Pch
    P
    • Ptt_Pch
    • Author
    • Known Participant
    • 91 replies
    • April 26, 2018

    My pleasure 🙂 :winking_face: !



    I must agree :grinning_face_with_smiling_eyes: !

    I’ve got a little time to explore your demo base and I must say that it is pretty neat :grinning_face_with_smiling_eyes: .

    I haven’t implemented it in my “Pets” base yet, but so far, after adapting the SET_TIMEZONE to my own Timezone and creating an exemple record an modifying the formula of the of {TodayOpen} in the [Shops]table, I can say it’s efficient :grinning_face_with_smiling_eyes: !


    I’ve just got one question 😋


    Is there a way for me to add possible midday closure ? 😊

    For exemple, concerning a store that have this kind of schedule : 8h30 > 12h > Closed > 13h30 > 18h.


    I’ve tried to go from 7 pairs of opening/closing fields to 14.

    (For example : {SundayOpen 1}, {SundayClose 1}, {SundayOpen 2}, {SundayClose 2}, etc…)

    I’ve successfully modified the {Hours::Hours} field so it lists the added closing/opening time too :winking_face: …


    What I can’t seem to find is a way to link those added fields to the {TodayOpen} rollup fields in the [Shops] table.


    In fact, I’m just not sure of what to do after that point 😓


    But other than that question, it’s working great ! 🙂

    P
    • Ptt_Pch
    • Author
    • Known Participant
    • 91 replies
    • April 26, 2018
    Ptt_Pch wrote:

    My pleasure 🙂 :winking_face: !



    I must agree :grinning_face_with_smiling_eyes: !

    I’ve got a little time to explore your demo base and I must say that it is pretty neat :grinning_face_with_smiling_eyes: .

    I haven’t implemented it in my “Pets” base yet, but so far, after adapting the SET_TIMEZONE to my own Timezone and creating an exemple record an modifying the formula of the of {TodayOpen} in the [Shops]table, I can say it’s efficient :grinning_face_with_smiling_eyes: !


    I’ve just got one question 😋


    Is there a way for me to add possible midday closure ? 😊

    For exemple, concerning a store that have this kind of schedule : 8h30 > 12h > Closed > 13h30 > 18h.


    I’ve tried to go from 7 pairs of opening/closing fields to 14.

    (For example : {SundayOpen 1}, {SundayClose 1}, {SundayOpen 2}, {SundayClose 2}, etc…)

    I’ve successfully modified the {Hours::Hours} field so it lists the added closing/opening time too :winking_face: …


    What I can’t seem to find is a way to link those added fields to the {TodayOpen} rollup fields in the [Shops] table.


    In fact, I’m just not sure of what to do after that point 😓


    But other than that question, it’s working great ! 🙂


    Well, I spoke too soon :grinning_face_with_smiling_eyes: !

    I’ve found a way to integrated the possible mid-day closure of a shop in the [Shops] table :grinning_face_with_smiling_eyes: !


    I duplicated the now modified {Hours::Hours}, which has simply become the {Hours::Hours} copy field.




    • In the{Hours::Hours} field, I modified the formula to “link” the field {DayOfTheWeekOpen 1} (for example, the {SundayOpen 1}) and the field{DayOfTheWeekClose 1} (for example, the {SundayClose 1}).




    • In the {Hours::Hours} copy field, I modified the formula to “link” the field {DayOfTheWeekOpen 2} (for example, the {SundayOpen 2}) and the field {DayOfTheWeekClose 2} (for example, the {SundayClose 2}).




    Put differently I just divise in 2 the original {Hours::Hours} :winking_face:


    On the [Shops] table, I just did the same thing with {TodayOpen} rollup field and {TodayClose} field, creating {TodayOpen 1},{TodayClose 1}, {TodayOpen 2} and {TodayClose 2}.




    • {TodayOpen 1} & {TodayClose 1} are rolling up the {Hours::Hours}


    • {TodayOpen 2} & {TodayClose 2} are rolling up the {Hours::Hours} copy


    I’ll continue my exploration tomorrow 🙂 !


    But many thanks again 🙂 !

    W
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • April 27, 2018
    Ptt_Pch wrote:

    Well, I spoke too soon :grinning_face_with_smiling_eyes: !

    I’ve found a way to integrated the possible mid-day closure of a shop in the [Shops] table :grinning_face_with_smiling_eyes: !


    I duplicated the now modified {Hours::Hours}, which has simply become the {Hours::Hours} copy field.




    • In the{Hours::Hours} field, I modified the formula to “link” the field {DayOfTheWeekOpen 1} (for example, the {SundayOpen 1}) and the field{DayOfTheWeekClose 1} (for example, the {SundayClose 1}).




    • In the {Hours::Hours} copy field, I modified the formula to “link” the field {DayOfTheWeekOpen 2} (for example, the {SundayOpen 2}) and the field {DayOfTheWeekClose 2} (for example, the {SundayClose 2}).




    Put differently I just divise in 2 the original {Hours::Hours} :winking_face:


    On the [Shops] table, I just did the same thing with {TodayOpen} rollup field and {TodayClose} field, creating {TodayOpen 1},{TodayClose 1}, {TodayOpen 2} and {TodayClose 2}.




    • {TodayOpen 1} & {TodayClose 1} are rolling up the {Hours::Hours}


    • {TodayOpen 2} & {TodayClose 2} are rolling up the {Hours::Hours} copy


    I’ll continue my exploration tomorrow 🙂 !


    But many thanks again 🙂 !


    You know, I very nearly added a postscript to my previous post, saying, ‘Should you need to model midday closings, you’re on your own’ — but you seem to be doing just fine!


    You’re probably already doing this, but if not, when it comes to determining the shop’s current status, I’d treat the first open/close pair as the main one. That is, I’d start by checking to see if {DoW Open 1} = '0000' and, if so, marking the shop as closed on that day. Shops without a midday closing would have their closing time entered in {DoW Close 1}, with {DoW Open 2} and {DoW Close 2} set to blank or zero. (This latter is an arbitrary choice; someone else might prefer {DoW Close 2} to represent close of business, with the two intervening times zeroed out for businesses without a midday closing. Either approach is valid — but the formula provided later assumes the first method.)


    To determine current status is a little more difficult. If you look at the original formula, you’ll see it has the following structure:


    IF  

    TodayOpen = '0000' 

THEN

    Shop is closed

ELSE 

    IF 

        TodayOpen is Earlier than Now

    AND

        TodayClose is Later than Now

    THEN

        Shop is Open

    ELSE

        Shop is Closed

    What you need now is


    IF  

    TodayOpen1 = '0000' 

THEN

    Shop is closed

ELSE    

    IF 

        TodayOpen1 is Earlier than Now

    AND

        TodayClose1 is Later than Now

    THEN

        Shop is Open

    ELSE

        IF 

            TodayOpen2 is Earlier than Now

        AND

            TodayClose2 is Later than Now

        THEN

            Shop is Open

        ELSE

            Shop is Closed

    From your earlier message, it sounds as if your {Hours::Hours} field now contains 28 open or close times, as so: 0800|1200|1330|1700|… As you obviously know (I apologize for being so pedantic, but I also write for those who later find this thread), this means each day’s initial opening time is now 20 characters offset from the previous day’s, rather than 10, with the day’s second opening and closing times offset by 11 and 16 days, respectively, from its first.


    I think the following formula gives you what you want, but I’ve not tested it. (Feel free to skip it and create your own variant, as you’ll need to change the timezone, at least, to get mine to work.)


    IF(

    MID(

        values&'',

        VALUE(

            DATETIME_FORMAT(

                SET_TIMEZONE(

                    NOW(),

                    'America/Los_Angeles'

                    ),

                'd'

                )

            )*10+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+1,

                            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'

            )

        )

    )

    I’m glad this worked for you and you’ve been able to adapt it to your needs!

    P
    1 person likes this
    • P
      Ptt_Pch
    P
    • Ptt_Pch
    • Author
    • Known Participant
    • 91 replies
    • April 27, 2018
    W_Vann_Hall wrote:

    You know, I very nearly added a postscript to my previous post, saying, ‘Should you need to model midday closings, you’re on your own’ — but you seem to be doing just fine!


    You’re probably already doing this, but if not, when it comes to determining the shop’s current status, I’d treat the first open/close pair as the main one. That is, I’d start by checking to see if {DoW Open 1} = '0000' and, if so, marking the shop as closed on that day. Shops without a midday closing would have their closing time entered in {DoW Close 1}, with {DoW Open 2} and {DoW Close 2} set to blank or zero. (This latter is an arbitrary choice; someone else might prefer {DoW Close 2} to represent close of business, with the two intervening times zeroed out for businesses without a midday closing. Either approach is valid — but the formula provided later assumes the first method.)


    To determine current status is a little more difficult. If you look at the original formula, you’ll see it has the following structure:


    IF  

    TodayOpen = '0000' 

THEN

    Shop is closed

ELSE 

    IF 

        TodayOpen is Earlier than Now

    AND

        TodayClose is Later than Now

    THEN

        Shop is Open

    ELSE

        Shop is Closed

    What you need now is


    IF  

    TodayOpen1 = '0000' 

THEN

    Shop is closed

ELSE    

    IF 

        TodayOpen1 is Earlier than Now

    AND

        TodayClose1 is Later than Now

    THEN

        Shop is Open

    ELSE

        IF 

            TodayOpen2 is Earlier than Now

        AND

            TodayClose2 is Later than Now

        THEN

            Shop is Open

        ELSE

            Shop is Closed

    From your earlier message, it sounds as if your {Hours::Hours} field now contains 28 open or close times, as so: 0800|1200|1330|1700|… As you obviously know (I apologize for being so pedantic, but I also write for those who later find this thread), this means each day’s initial opening time is now 20 characters offset from the previous day’s, rather than 10, with the day’s second opening and closing times offset by 11 and 16 days, respectively, from its first.


    I think the following formula gives you what you want, but I’ve not tested it. (Feel free to skip it and create your own variant, as you’ll need to change the timezone, at least, to get mine to work.)


    IF(

    MID(

        values&'',

        VALUE(

            DATETIME_FORMAT(

                SET_TIMEZONE(

                    NOW(),

                    'America/Los_Angeles'

                    ),

                'd'

                )

            )*10+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+1,

                            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'

            )

        )

    )

    I’m glad this worked for you and you’ve been able to adapt it to your needs!



    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 🙂

    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 🤔 , 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 😋

    • 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: 🙂 !

    D
    1 person likes this
    • D
      David_Vasandani
    P
    • Ptt_Pch
    • Author
    • Known Participant
    • 91 replies
    • May 8, 2018

    Just in case, someone, somewhere, sometime, wish to know what happened with my initial request, you’ll find out => here :winking_face:

    Reply


    Most helpful members this week

    Terms & Conditions