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"))
=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"),""))
=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?
Thanks, and belowBelow is sample google spreadsheetGoogle Spreadsheet that shows what exactly is my problem.
1: http://Hi,%20I'm%20trying%20to%20check%20how%20much%20of%20the%20my%20data%20are%20collected%20during%20worktime.%20(between%2010am~5pm).%20So%20I%20made%20a%20IF%20function%20for%20this,%20which%20I%20had%20to%20drag%20down%20every%20single%20time%20when%20new%20rows%20are%20added.%20%20My%20original%20function%20was%20like%20this.%20%20%20%20=IF(AND(B4:B%3ETIMEVALUE(%2210:00:00%22),B4:B%3CTIMEVALUE(%2217:00:00%22)),%22Yes%22,%22Not%22))%20%20And%20I%20get%20to%20know%20this%20awesome%20arrayformula%20function%20that%20automatically%20fills%20up%20to%20the%20newly%20added%20rows,%20and%20applied%20it%20here.%20%20=ArrayFormula(IF(filter(A4:A,A4:A%3C%3E%22%22),IF(AND(B4:B%3ETIMEVALUE(%2210:00:00%22),B4:B%3CTIMEVALUE(%2217:00:00%22)),%22Yes%22,%22Not%22),%22%22))%20%20%20Sorry%20about%20my%20messy%20ass%20function,%20but%20this%20is%20pretty%20much%20all%20i%20can%20think%20of.%20%20Also,%20I%20added%20FILTER%20function%20so%20that%20arrayformula%20stops%20before%20the%20blank%20rows.%20%20Apparently%20it%20does%20shows%20some%20values,%20but%20it%20doesn't%20show%20any%20correct%20values%20on%20my%20sheet...%20%20%20Can%20I%20please%20get%20some%20help%20with%20this?%20%20Thanks,%20and%20below%20is%20sample%20google%20spreadsheet%20that%20shows%20what%20exactly%20is%20my%20problem.%20%201:%20https://docs.google.com/a/meshkorea.net/spreadsheets/d/14X9zYMuU_0tJE43W8lHFO3F_yjA3qnVq1LhGLex1jqo/edit?usp=sharing
