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

Improve this question
1
  • 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
    Commented Sep 16, 2017 at 10:36

1 Answer 1

Reset to default
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.

Improve this answer
6
  • thanks, but this function still shows wrong values. It says 'yes' for the data collected at 11PM.
    – Michael Kim
    Commented Sep 16, 2017 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
    Commented Sep 16, 2017 at 10:34
  • Wait.. I'll do a counta on this formula
    – TheMaster
    Commented Sep 16, 2017 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
    Commented Sep 16, 2017 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
    Commented Sep 16, 2017 at 10:52

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.