There’s several ways to handle this depending on how flexible you need it to be, or if the days and times would ever change. What I show below is maintainable, but may take a little bit to fully understand it and how the IF column works. But in it’s simplest (least amount of columns) form, I would do it like this. It could be changed to make it easier to understand, but it would take a few more columns to build it out.
- First I would create a math column to figure out the current weekday number:
- Next I would create another math column to figure out the current hour:
- Finally I would create an IF column to use those values to determine if the business is open or closed. Keep in mind that this is based on the current date and time of the current user’s local timezone:
- What I do here is first check if the weekday is 1 (meaning Sunday), then return Closed.
- Next I check if the hour is before 9:00, then return Closed so we eliminate any hours less than 9.
- Next I check if the hour is on or after 21:00, then return Closed so we eliminate any hours greater than or equal to 21.
- Next I check if the hour is before 15:00, then return Open since any remaining unchecked hours prior to 15:00 on Monday through Saturday are Open.
- Next I check if the weekday is 7 (meaning Saturday), then return Closed because the only hours left to check are between 15 and 21 and Saturday is closed during that time.
- Finally I check if the hour is on or after 17:00, then return Open because all that’s left to check are the times between 17 and 21 on Monday through Friday.
- Anything between 15:00 and 17:00 will fall into the ELSE and return Closed.
