Hi folks, anyone know how I can filter columns by time of day in Numbers? It gives me several filtering options - text, numbers, date, duration - but I can't seem to filter by time of day specifically. I'm trying to filter half hourly temperature readings over a duration of months to give me only the readings that occurred between 11 am and 5 pm and I'm having a tough time figuring this out. Help most appreciated.
MacBook Air, iOS 6.1.4
Hi Neewok,
Add a column to extract the Time Value from each Date and Time value. The result is a number between 0 and 1, equivalent to the fraction of a (24 hour) day that has passed at that time. You shoud be able to filter on that number, or extract the values read between the two values to a summary table.
For the example, I chose to record temperature readings at three hour intervals to reduce the number of rows needed to include several days, and changed the time window to 11AM to 6 PM to increase the number of 'eligible' readings.
Formulas:
Data table has one formula, used to create the index that marks the rows to be copied to the Summary table:
C2, and filled down: =IF(AND(TIMEVALUE(A)>=11/24,TIMEVALUE(A)<=0.75),MAX($C$1:C1)+1,"")
The IF part sets the window that passes readings taken between 11 AM (timevalue: 11/24) and 6 PM (timevalue 18/24 = 0.75) inclusive. For your stated window, sustitute 17/24 for 0.75.
For readings with timevalues within that range, the count in the index column is incremented by 1, for values outside the range, the formula inserts a null string ( "" ).
Summary uses a LOOKUP formula to retrieve the date and time and the temperature reading from each indexed line of Date.
A2: =IF(ROW()-1>MAX(Data::$C),"",LOOKUP(ROW()-1,Data::$C,Data::$A))
B2: =IF(ROW()-1>MAX(Data::$C),"",LOOKUP(ROW()-1,Data::$C,Data::$B))
Fill both formulas to the end of their respective columns.
The IF part here is a switch, used to turn off the search and insert a null string when all of the marked rows have been collected.
Regards,
Barry
Hi Neewok,
Add a column to extract the Time Value from each Date and Time value. The result is a number between 0 and 1, equivalent to the fraction of a (24 hour) day that has passed at that time. You shoud be able to filter on that number, or extract the values read between the two values to a summary table.
For the example, I chose to record temperature readings at three hour intervals to reduce the number of rows needed to include several days, and changed the time window to 11AM to 6 PM to increase the number of 'eligible' readings.
Formulas:
Data table has one formula, used to create the index that marks the rows to be copied to the Summary table:
C2, and filled down: =IF(AND(TIMEVALUE(A)>=11/24,TIMEVALUE(A)<=0.75),MAX($C$1:C1)+1,"")
The IF part sets the window that passes readings taken between 11 AM (timevalue: 11/24) and 6 PM (timevalue 18/24 = 0.75) inclusive. For your stated window, sustitute 17/24 for 0.75.
For readings with timevalues within that range, the count in the index column is incremented by 1, for values outside the range, the formula inserts a null string ( "" ).
Summary uses a LOOKUP formula to retrieve the date and time and the temperature reading from each indexed line of Date.
A2: =IF(ROW()-1>MAX(Data::$C),"",LOOKUP(ROW()-1,Data::$C,Data::$A))
B2: =IF(ROW()-1>MAX(Data::$C),"",LOOKUP(ROW()-1,Data::$C,Data::$B))
Fill both formulas to the end of their respective columns.
The IF part here is a switch, used to turn off the search and insert a null string when all of the marked rows have been collected.
Regards,
Barry
Many thanks Barry. This looked a bit complex but I created an index column from the reading times - they were already separated into date and time format - and used the timevalue function to give me a function to filter by. Problem solved! Thanks again.
Filtering columns by time of day
