Skip to main content

Hi everyone,


I have a Stacker app, linked of course to Airtable, for which my colleagues use to plan events, sync these to internal calendars, etc.


Some of these events run “Out of Hours”, i.e. starting or running into Saturdays or Sundays, or starting or running past 5pm Monday to Friday, and for compliance purposes, these events needs some special email notifications to fire to certain people.


One workaround would be simply to have users mark a checkbox field to say “my event is out of hours”, but this still has the possibility of user error (and I’d like to streamline my Stacker app as much as possible).


So, theorising a workaround, I’ve created four formula fields using DATETIME_FORMAT to extract the Start day, End day, Start time, and End time independently of any date and those values being combined in the date fields, which what my users configure. This has allowed me to get text values for “Saturday” and “Sunday”, and it’s easy enough to configure an automation for these, however, I’m stumped as to if it’s possible to configure an automation to recognise when the Start time or End time fields are greater than 5pm, e.g. “> 16:59”.


I don’t suppose this might be something anyone else has encountered before, or might any ideas for?


Much appreciated,


Matt

Hi @Strategy_Team, I’ve attached a formula below that will identify whether a date field’s time is past 5 pm. You’ll need to make sure that all your date fields are using GMT though; weirdness will ensue if you don’t



Lemme know if you have any questions about how it works


IS_AFTER(

  Date,

  DATEADD(

    DATEADD(

      DATETIME_PARSE(

        DATETIME_FORMAT(

          Date,

          "DD MM YYYY"

        ),

        "DD MM YYYY"

      ),

      17,

      "hours"

    ),

    -1,

    "minutes"

  )

)

Hi @Strategy_Team, I’ve attached a formula below that will identify whether a date field’s time is past 5 pm. You’ll need to make sure that all your date fields are using GMT though; weirdness will ensue if you don’t



Lemme know if you have any questions about how it works


IS_AFTER(

  Date,

  DATEADD(

    DATEADD(

      DATETIME_PARSE(

        DATETIME_FORMAT(

          Date,

          "DD MM YYYY"

        ),

        "DD MM YYYY"

      ),

      17,

      "hours"

    ),

    -1,

    "minutes"

  )

)

@Adam_TheTimeSavingCo genius, thank you so much! 😀 (Although I can’t fully pretend to understand it!)


All my date fields are set to local time for users as there was previously an issue with using GMT and the times being recorded in Airtable an hour wrong compared to how they were selected in Stacker. We’re technically on British Summer Time now, which as far as I’m aware should be equivalent to GMT +1, although my Airtable base isn’t recognising the difference as your formula is already working perfectly…


Assuming this might present a slight issue when the clocks go back in winter, but that it could be rectified with a +/-1 adjustment to your formula? Hopefully there’s no other future issues I might need to think of!


Many thanks again,


Matt

@Adam_TheTimeSavingCo genius, thank you so much! 😀 (Although I can’t fully pretend to understand it!)


All my date fields are set to local time for users as there was previously an issue with using GMT and the times being recorded in Airtable an hour wrong compared to how they were selected in Stacker. We’re technically on British Summer Time now, which as far as I’m aware should be equivalent to GMT +1, although my Airtable base isn’t recognising the difference as your formula is already working perfectly…


Assuming this might present a slight issue when the clocks go back in winter, but that it could be rectified with a +/-1 adjustment to your formula? Hopefully there’s no other future issues I might need to think of!


Many thanks again,


Matt


@Strategy_Team


Ahh fair enough. Yeah, with GMT +1 you’ll need to change that 17 to 16 and it should work fine for you


Timezone stuff always makes me go a bit cross-eyed honestly; I got a headache trying to figure out whether it should be 16 or 18 sigh


I put this together to figure it out, so I figure you might as well have it in case you hit any issues in the future


You’ll be able to get all the formulas after you duplicate the base

@Strategy_Team


Ahh fair enough. Yeah, with GMT +1 you’ll need to change that 17 to 16 and it should work fine for you


Timezone stuff always makes me go a bit cross-eyed honestly; I got a headache trying to figure out whether it should be 16 or 18 sigh


I put this together to figure it out, so I figure you might as well have it in case you hit any issues in the future


You’ll be able to get all the formulas after you duplicate the base


@Adam_TheTimeSavingCo That’s really kind of you to put together. I’ve made a copy of the base (and put a reminder in my calendar to check my formulas against yours on October 30) - thank you again!


Matt

Reply


Terms & Conditions