PM time in formula...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following array formula:
{=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00
AM")))}. Works fine for AM time values, but when formula runs across a PM
time value in the range, its ignored - any clues as to why this is happending?

Thank you.

Mark :)
 
any clues as to why this is happending?

Because the formula is written to check only a single specific time: 6:00
AM.

That's what you asked for in your original post.

There are better ways to do this. See your original post.

Biff
 
Thnak you T.

I understand, but the formula dosn't count properly when I replace the 6:00
AM with 6:00 PM - still a single value (i.e. give me the total count of all
those entires where the date is "3/10/2007" and time is "6:00 PM").

Mark
 
Thnak you again. I should have clarified in my original post. Bottom line
is the fomrula returns a 0 count when a PM value is used in the fomrula and
there is a coresponding PM value in the time range - why?

Mark :)
 
Ok....

You don't need to use an array formula to do this. Sometimes people forget
that when they edit an array formula it must be re-entered as an array.
Since this can be done without an array formula that will eliminate at least
that much of the potential problems.

You'd be better off using cells to hold the criteria. Then, you only need to
change the cells instead of editing the formula. So:

A1 = some date like 3/10/2007
A2 = some time like 6:00 PM

=SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2))

If this worked on 6:00 AM but does not work on 6:00 PM then there is
something going on with your times (or even the dates). Are you sure they're
all times/dates? Although in the formula you posted the Datevalue/Timevalue
function would convert TEXT date/times into NUMERIC date/times. Are these
date/times calculated? Are they filled by a series fill or drag copied? I've
seen situations where 6:00 PM does not equal 6:00 PM due to series filling
and drag copying that causes rounding issues.

Biff
 
T. Valko said:
You don't need to use an array formula to do this.
Click to expand...

Well, technically SUMPRODUCT() *is* an array function (one reason you
can't use entire columns pre-XL07). You just don't have to use
CTRL-SHIFT-ENTER with it.
 
Thank you.

I went back an manually re-entered the time values in my range (they were
filled) and the fomrula now works.

Thank you again.

Mark :)
 
Glad to hear you got it to work!

Depending on how you want to fill a series of times it's best to use an
incremental formula but I've even seen this method cause rounding issues.
The only guaranteed way to avoid this issue is to manually enter the series
which can be a real PITA.

Biff
 
Back
Top