Looking for a function to showcase different data depending on day

Ask for Help
1

Hey guys,

I have a restaurant directory and was wondering how can I create a function that depending on the day, it shows what time the restaurant is open.

For example:

Monday- 9AM- 5PM
Tuesday 7AM-4PM etc.

@Jeff_Hager @George_B any takes?

Thanks.

2

Take a look at the sheet called Hours on this spreadsheet. That should do what you want. (Ignore the other tabs, I just tacked it on to this spreadsheet instead of creating a new one.)

1 Like
3

Awesome,

Ill look at it, thank you so much George!

Last Q, what if restaurants have different hours, do I have to add something to the function?
Also, I should put this on a separate tab, and on the column of a respective restaurant copy it?

I made this sheet for example: https://docs.google.com/spreadsheets/d/1ted4gyZB5HsWr_Yk9HlagY9E8kvwfWBTtgoDK7MNLw8/edit?usp=sharing

I plan to use the “answer” and place it in another tab.

4

I guess you would have to list the hours for each restaurant in 7 columns on the restaurant individual details row. Then modify the formula a bit, probably use the hlookup() function instead of the lookup() that I used.

1 Like
5

Awesome,
Yea I added the data for each restaurant.

Thanks!

6

I had another light bulb moment. Check out the updated spreadsheet, sheet HoursRow, cell J1. That should work real well for you.

1 Like
7

Woww that you so much!
The main benefit is that with this new formula, you dont need a cell with the =today() function?

Pardon my ignorance

8

The today() is within the formula. Look up the OFFSET() function in the Google Sheet docs to see how that works. The Weekday() function returns the day of the week for a given date. The date that is used is today(). The minus one is needed because of the zero based array that the Offset() function uses.

Keep in mind to make sure your spreadsheet has the proper Time Zone set in the File/Spreadsheet Settings. It should match what timezone all your restaurants are in. If you were to happen to have restaurants in different time zones you would have to factor that into the calculation by adding or subtracting the proper number of hours from what Today() is returning.

={"Todays Hrs";ARRAYFORMULA(OFFSET(C2:C,0,Weekday(today())-1))}
9

I see, so far I am only adding restaurants in the NYC area, but will keep this in mind.

Thank you!