I own vacation rentals in a small tourist town that just has a few restaurants. When my guests are looking for a place to eat, it’s difficult to know know which ones are open at any given time. I’d like to create a simple directory of restaurants with the option to show a listing of restaurants that are open “now”. If each listing includes the days and hours the restaurant is open, is it possible to include a function that can filter which ones are open at the current day and time?
What do your columns look like for days and hours?
I haven’t built anything yet. Would be starting from scratch.
The NOW() function will give you the current date/time.
I would add a column in your spreadsheet called ‘Open Now’. This column would have an IF formula that compares if NOW() is greater than ‘Business Hour Start Date/Time’ and less than ‘Business Hour End Date/Time’, then it is open, else No.
Yes, but the NOW() formula will not refresh in Glide and Gsheet until you edit some data via Glide. So it will not work.
Have you tried this?
In your google sheet, set File > Spreadsheet settings > Calculation > Recalculation = “On edit and every minute”.
1 Like
Mmm don’t think this works actually. Might need the pro app that refreshes every couple of minutes.
1 Like
Yep
Excellent. That’s exactly what I needed to know.
If I add a column that lists the days of the week that a business is closed (e.g. monday tuesday wednesday) is there a way to detect what day of the week today is? The TODAY() formula gives todays date but not which day of the week.
Ahh. Never mind. Google found the answer for me. This does the trick…
=CHOOSE( weekday(H1), “Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”)
I think there might be a way to do what you want. I might be able to work on it this weekend. Basically I’m thinking 2 date columns that will be built in the sheet. One for start date/time and one for end date/time. The date will always be Today(). They will be built based one days and hours available for each restaurant. Then I’m hoping you can use >= and <= comparisons on the filter comparing the dates to now().
That would be awesome if you could take a shot at it when you have time, Jeff.
Based on the earlier advice I started playing around with it and got something that resembles what I had in mind. My solution ended up very long and convoluted and I’m sure there’s a much more elegant way to achieve the same result.
@bjgray I think this spreadsheet I put together is what you are looking for. You do need to have a column for each day of the week and format the times in MM:HH-MM:HH format. I added the last two columns so I could test the results as it operates on NOW() for the weekday and I needed to change the formula to accept a hard day of week number in order to test it. https://docs.google.com/spreadsheets/d/1bF9UuJdowb6tlqgoy5y7WNygF22u_wna1ww76-tQxZc/edit?usp=sharing
2 Likes
I took a slightly different approach from @George_B’s example. I am building an Open From and Open To column from today’s date and the IF’s to determine with day column to get the hours. I then use these values with the glide filters to check if the opening time from the sheet is before now and the closing time in the sheet is after now. That way even if the app only pulls updates from the sheet once a day, the comparison will happen inside of glide. This will allow the the entries to immediately hide once the restaurant is closed.
4 Likes
I love how there are always more than one way to approach a problem. So with this method you would only see a list of restaurants that are currently open. The only downside to this method would be that someone may miss a restaurant that is just about to open or see ones that may interest them but are closed. Obviously you would provide them with another tab that would show all the restaurants so this would be a non issue.
2 Likes
Your solution is working perfectly! Very efficient and clean.
Do you think there’s a user-friendly way to present all the hours in a property’s detail view, and is there a formula that could convert the hours to 12 hour format?
Amazing! No idea how it works but it works beautifully.
I’m running into a problem with days that a restaurant is closed. If the cell is left blank the open and close times default to 12:00:00 AM.
fixed