Business Hours if statement help needed!

Hi. Essentially I have a spreadsheet that I have tables in. For examples purposes all names of tables will be alphabetical. I have Sheet 1 (a directory) with a list of a business including their contact info. On sheet 2 i have created a table for each business I will link to Sheet 1, these tables include rows with the business hours. Example:

COLUMN A | COLUMN B | COLUMN C

MONDAY | 07:30 | 16:30 |
TUESDAY| 07:30 | 16:30 |
WEDNESDAY | 07:30 | 16:30 |
ETC
ETC
SATURDAY | CLOSED |

Sheet 1 will look like this (The main sheet)

BUSINESS NAME | TODAY | column C | CONTACT

BUSINESS DEPOT | (INSERT HOURS FROM SHEET 2) | If statement | PHONE #
STATE FARM | (INSERT HOURS FROM SHEET 2) | IF statement | PHONE #
WALMART | (INSTERT HOURS OF WALMART FROM SHEET 2) | IF statement | PHONE #
ETC

---------

I'm good with the contact info. However my main goal is to have an if statement for row c on sheet 1 table. If the hours of the current day and time correlate with the hours of the business operation hours then say "OPEN" or "CLOSED". For example: If BUSINESS DEPOT is open from monday-fri 07:30 and closes at 16:30 and the current time of the day [AKA now()] is within that range then return "OPEN" in COUMN C of SHEET 1. If it is not open because the current day is sunday then return "CLOSE" in COLUMN C of SHEET 1. I was doing good but HLOOKUP got me confused. So far I have to do something but doesn't help at all. I basically want COLUMN B of SHEET 1 to list the current days operation hours of that specific business. So if I am looking at the spreadsheet at 16:35 of a Monday I want SHEET 1 to look like this:

BUSINESS DEPOT | 07:30-16:30 | OPEN | PHONE | 123-456-7890

But if I'm looking at the same sheet on a saturday I want it to return this:

BUSINESS DEPOT | | CLOSED | PHONE | 123-456-6907

I was doing good until the if statement included the weekends and I had to start incorporating HLOOKUP which got me really messed up. If any of this makes sense please help me out!

this is what I have so far...but I know it's far away from what I need, because I also want COLUMN C of SHEET 1 to state CLOSED for holidays. I also don't know how to get this code to reference sheet 2's business operation hours for the workday()

=IF(OR('Hours/Contact'::ACS :: B1>NOW(),'Hours/Contact'::ACS :: B2>NOW(),'Hours/Contact'::ACS :: B3>NOW(),'Hours/Contact'::ACS :: B4>NOW(),'Hours/Contact'::ACS :: B5>NOW(),'Hours/Contact'::ACS :: C1<NOW(),'Hours/Contact'::ACS :: C2<NOW(),'Hours/Contact'::ACS :: C3<NOW(),'Hours/Contact'::ACS :: C4<NOW(),'Hours/Contact'::ACS :: C5, WEEKDAY(TODAY(),2)=1, WEEKDAY(TODAY(),2)=2,WEEKDAY(TODAY(),2)=3,WEEKDAY(TODAY(),2)=4,WEEKDAY(TODAY(),2)=5), "OPEN", "CLOSED")

in a1 CHOOSE(WEEKDAY(TODAY()),"Sunday","MONDAY","TUESDAY","WEDNSDAY","THURSDAY","FRIDAY","SATURDAY")

in b1 time 07:30

in C1 =IF(AND(B1>= OFFSET(B1,MATCH(A1,A2:A8,0),0),B1<= OFFSET(B1,MATCH(A1,A2:A8,0),1)),"Open","Closed")


in A2 to a8 mon to sat all uper case like "MONDAY"
IN B2 TO B8 OPENING HOURS
IN C2 TO C8 CLOSING TIMES

Brilliant!!!! Thank you so much for your help, I really mean that, thank you. My only remaining question does this formula account for holidays? If all businesses are closed on holidays I add them in C1 correct? and not A1

Originally Posted by mehmetcik

in a1 CHOOSE(WEEKDAY(TODAY()),"Sunday","MONDAY","TUESDAY","WEDNSDAY","THURSDAY","FRIDAY","SATURDAY")

in b1 time 07:30

in C1 =IF(AND(B1>= OFFSET(B1,MATCH(A1,A2:A8,0),0),B1<= OFFSET(B1,MATCH(A1,A2:A8,0),1)),"Open","Closed")


in A2 to a8 mon to sat all uper case like "MONDAY"
IN B2 TO B8 OPENING HOURS
IN C2 TO C8 CLOSING TIMES

Also, That works when using the business hours on the same sheet. My main goal is to have SHEET 1 just show

BUSINESS NAME | BUSINESS HOURS FOR DAY LOOKING AT SPREADSHEET | OPEN/CLOSE | CONTACT

Then I want to have in sheet 2 a table of business hours with a2-a8 listing days of week and b2-b8 opening hours and c2-c8 closing hours.

How do I reference on Sheet 1 column b todays operating hours for that day

And then how =if(and(.... in column c of sheet 1 to state OPEN or CLOSE

When I tried that it states b3 isn't a referenced cell and it was expecting 0 but showed a time.

Does that make sense? Im sorry if I am making this harder than it should be.

Ok

Front Sheet.

A1 = day of the week MONDAY
B1 is Time 07:00
C1 is =IF(AND(B1>= OFFSET(Data!B1,MATCH('Front Sheet'!A1,Data!A2:A8,0),0),B1<= OFFSET(Data!B1,MATCH('Front Sheet'!A1,Data!A2:A8,0),1)),"Open","Closed")


Datasheet

days in a2 to a 8 Monday to Sunday
opening hours in b2 to b8
closing hours in c2 to c8

Holidays are really complicated.

You would have to put formulas in your data sheet to change the opening hours if the dates match specified holidays.

I will think on that and get back to you

roger that. I'll convert it to excel tonight for those who can't open pictures. So far mehmetcik solution is working in my favor but I will convert it tonight.

Ok.

I have included three break periods.

I have also simplified all the formulae

I have illustrated two customers

Good Luck.

Thank you. I appreciate everything that you have done for me concerning this and I owe you one. I can't say it enough, thank you