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))
- Conversion operator and bool operator for if init expression
- Mysql How to only select from a column if the column exists
- Unable to use IF statement in plpgsql
- Trouble Excluding Named Worksheets from a Loop
- Adding only some data labels and a total value at the top of my stacked bar chart
- Good C-coding style for multiple lines if conditions
- If statement to check if an array is not in another array
- Determining if a string contains a word
- How can I make a def function with my "if" statements?
- How to assign a variable in an IF condition, and then return it?
- Using or in if statement (Python)
- C# Creating a Square
- Return TRUE/FALSE column for checking if a set of columns contains a value/string
- Does Go have "if x in" construct similar to Python?
- Does if (myBool == true) differ computational wise to if(myBool)?
- Write a Basic Car rental program on java / problems
- Writing a "Chessboard" function in Python that prints out requested length of binary
- Will my program execute "else" even if it executed my "if"?
- Why Java does not see that Integers are equal?
- How to have the Xcode 3.1 compiler warn of assignment operator in an if statement?
- Nested if Statement good or bad?
- How to write if statement in .tmux.conf to set different options for different tmux versions?
- is not null Vs != null (Unity C#)
- VBA "does not equal" code not stepping through IF statement as expected
- Avoiding nested if else statement ladder in Python
- I want to take output from a function to use in a IF statement in another function
- What actually happens when we put conditions without [] (brackets) in if else block in a bash script?
- C++ The if statement is still true even when only one condition is met
- Fortran if statement errors
- How can I change certain values in a column based on multiple conditions, leaving others as they are if the conditions aren't met?