There is a client’s working time, it is necessary to display the client is currently open or closed. Is it possible?
-
Welcome. It's not clear what you are asking for. Are you looking for a formula? What you have tried so far? Have you done any search/research effort as is suggested in How to Ask? – Rubén♦ Nov 23 '19 at 21:38
-
Hello! I am looking for =arrayformula formula if it is possible. – Vladimir Melnikov Nov 24 '19 at 8:25
-
The current answer already includes an arrayformula. If you need further help add some sample input/output data. – Rubén♦ Nov 24 '19 at 14:35
Simple formula put in cell C1
=arrayformula({"isOpen?";if(A2:A="","",if((timevalue(now())>=A2:A)*(timevalue(now())<=B2:B),"Open","Closed"))})
as an added bonus this will tell you how many hours to open or close in D1
=arrayformula({"Status?";if(A2:A="","",if(C2:C="Closed","Opens in "&text(24*((A2:A<timevalue(now()))+A2:A-timevalue(now())),"#.#")&" hours","Closes in "&text(24*((B2:B<timevalue(now()))+B2:B-timevalue(now())),"#.#")&" hours"))})
-
Hello! Thank you for the formulas, but there is one problem. When I use this, the formula begins to add an infinite number of lines. Maybe I'm doing something wrong? – Vladimir Melnikov Nov 24 '19 at 8:22
-
I figured it out, I should put the formula in A1 to make it work. Thank you very much! – Vladimir Melnikov Nov 24 '19 at 8:30
-
The formula is not always correct, for example, if the client’s opening time is 18:00 and the closing time is 2:00 and now is working time, then it will show that it is closed. – Vladimir Melnikov Nov 24 '19 at 8:58
-
I wonder what script is used in google maps to show open place or closed :-) – Vladimir Melnikov Nov 24 '19 at 8:59
-
@VladimirMelnikov if you put opens in column A and closes in column B the formula works fine. – CodeCamper Nov 24 '19 at 20:48