Using time in conditional formulas

  • Thread starter Thread starter josie
  • Start date Start date
J

josie

I'd like to analyze data by time period and don't know how to use time
in conditional formulas I've been using ... like:
=SUM(($D$3:$D$4403>0)*($D$3:$D$4403<4)*1). Ideally, I'd like to be able
to using multi-condition formulas, with one criterion being time (to
look at data by time period).

Example of monster equation: =AVERAGEA(IF('Data Entry
Sheet'!B3:B66=1,IF('Data Entry Sheet'!AP3:AS66>0,IF('Data Entry
Sheet'!AP3:AS66<9,'Data Entry Sheet'!AP3:AS66),(IF('Data Entry
Sheet'!B3:B66=1,IF('Data Entry Sheet'!AU3:AV66>0,IF('Data Entry
Sheet'!AU3:AV66<9,'Data Entry Sheet'!AU3:AV66)))))))

Yeah, I know it's ugly.

Can anyone help me understand how to use time in a conditional
formula??
 
josie said:
I'd like to analyze data by time period and don't know how to use time
in conditional formulas I've been using ... like:
=SUM(($D$3:$D$4403>0)*($D$3:$D$4403<4)*1). Ideally, I'd like to be able
to using multi-condition formulas, with one criterion being time (to
look at data by time period).

Example of monster equation: =AVERAGEA(IF('Data Entry
Sheet'!B3:B66=1,IF('Data Entry Sheet'!AP3:AS66>0,IF('Data Entry
Sheet'!AP3:AS66<9,'Data Entry Sheet'!AP3:AS66),(IF('Data Entry
Sheet'!B3:B66=1,IF('Data Entry Sheet'!AU3:AV66>0,IF('Data Entry
Sheet'!AU3:AV66<9,'Data Entry Sheet'!AU3:AV66)))))))

Yeah, I know it's ugly.

Can anyone help me understand how to use time in a conditional
formula??
Click to expand...

I will not try to address the detail of your situation, but just answer the
last sentence.
Instead of using a number in a comparison, you can use a time value, for
example A1<TIME(16,30,0).
The TIME function is like this: TIME(hours,minutes,seconds).
So, for example,
=IF(A1<TIME(16,30,0),1,2)
will give the result 1 if A1 contains a time before 16:30:00 and 2
otherwise.
 
Back
Top