There is a client’s working time, it is necessary to display the client is currently open or closed. Is it possible?

enter image description here
enter image description here

  • 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? Commented Nov 23, 2019 at 21:38
  • Hello! I am looking for =arrayformula formula if it is possible. Commented Nov 24, 2019 at 8:25
  • The current answer already includes an arrayformula. If you need further help add some sample input/output data. Commented Nov 24, 2019 at 14:35

1 Answer 1


Simple formula put in cell C1


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? Commented Nov 24, 2019 at 8:22
  • I figured it out, I should put the formula in A1 to make it work. Thank you very much! Commented Nov 24, 2019 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. Commented Nov 24, 2019 at 8:58
  • I wonder what script is used in google maps to show open place or closed :-) Commented Nov 24, 2019 at 8:59
  • @VladimirMelnikov if you put opens in column A and closes in column B the formula works fine.
    – CodeCamper
    Commented Nov 24, 2019 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.