0

I'm trying to check how much of the my data are collected during worktime. (between 10am~5pm). So I made a IF function for this, which I had to drag down every single time when new rows are added. My original function was like this:

=IF(AND(B4:B>TIMEVALUE("10:00:00"),B4:B<TIMEVALUE("17:00:00")),"Yes","Not"))

And I get to know this awesome arrayformula function that automatically fills up to the newly added rows, and applied it here:

=ArrayFormula(IF(filter(A4:A,A4:A<>""),IF(AND(B4:B>TIMEVALUE("10:00:00"),B4:B<TIMEVALUE("17:00:00")),"Yes","Not"),""))

Sorry about my messy, 'Inception-like' IF function, but this is pretty much all i can think of.

Also, I added FILTER function so that arrayformula stops before the blank rows.

Apparently it does shows some values, but it doesn't show any correct values on my sheet...

Conclusion

  • Can I please get some help with this ARRAYFORMULA(IF+FILTER+IF) Function?

  • Also, is there any way that I can show "weekend" on the data that are collected during the weekend?

Below is sample Google Spreadsheet that shows what exactly is my problem.

https://docs.google.com/a/meshkorea.net/spreadsheets/d/14X9zYMuU_0tJE43W8lHFO3F_yjA3qnVq1LhGLex1jqo/edit?usp=sharing

  • So I tried this, =ARRAYFORMULA(IF(filter(A4:A,A4:A<>""),IF((B4:B>TIMEVALUE("10:00:00"))*(B4:B<TIMEVALUE("17:00:00")),"Yes","Not"))) and now it works. but now, how can I make it to say "weekend" for the data collected during weekend? – Michael Kim Sep 16 '17 at 10:36
0

Try

  =ARRAYFORMULA(IF(A4:A="","",IF(B4:B>TIMEVALUE("10:00:00")*(B4:B<TIMEVALUE("17:00:00")),"Yes","Not")))

There's no room for FILTER here. It has other uses.

  • thanks, but this function still shows wrong values. It says 'yes' for the data collected at 11PM. – Michael Kim Sep 16 '17 at 10:30
  • Oh wait. I just added 1 () at the early timevalue, and it works. However, now it is causing reference error because it's filling all the empty rows with "", which seems empty but not really. My scripted sheet is now automatically adding values far beyond the last row, like 600~700 rows away from my last one. That's pretty much why i put filter on my function. – Michael Kim Sep 16 '17 at 10:34
  • Wait.. I'll do a counta on this formula – TheMaster Sep 16 '17 at 10:41
  • Will all the rows above the blank rows be filled always? I mean, Would the first blank only start at the end? – TheMaster Sep 16 '17 at 10:44
  • i actually solved this problem by using FILTER, and frankly I don't know why it works. lol here's my final work. Now I'm trying to figure out how can i make some data show 'weekend'. docs.google.com/spreadsheets/d/… – Michael Kim Sep 16 '17 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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