Return a specific value 'Open' where days are between Monday - Friday and time is >=8:00 & <19:00 all other days and times should return 'OOH'
Return a specific value 'Open' where days are between Monday - Friday and time is >=8:00 & <19:00 all other days and times should return 'OOH'
Open = >=8:00 & <19:00 & Monday to Friday inclusive OOH = >=19:01 & <7:59 & Monday to Friday & anytime on Saturday or Sunday
https://docs.google.com/spreadsheets/d/1tVhwBPBtT7a3RhjhVIi9UAqeCAIgBLYJKj5PchNdlyA/edit?usp=sharing
I currently tried these formulas independently but I would like one formula to do the job:
=INDEX(counta(IF((HOUR(A2:A)>=8)*(HOUR(A2:A)<19), "OPEN", "OOH")))
The above returns all data that I require considering the time only this is shown in Sheet1!B2:B
=query(countif(filter(text(Sheet1!A:A,"dddd"),len(Sheet1!A:A)),"Sunday"))
The above returns the number of Sundays which could then be removed from the data along with Saturdays however the data would be skewed if I did this as the times are considering all days at present.
Sheet1!C2:C should show either Open or OOH for each date/time
try:
=INDEX(LAMBDA(x, IF(NOT(REGEXMATCH(TEXT(x, "ddd"), "Sat|Sun"))*
(TIMEVALUE(x)>=("8:00"*1))*(TIMEVALUE(x)<("19:00"*1)), "Open", "OOH"))(A2:A))
