PM time in formula...
 

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 :)

PM time in formula...
 

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

"NWO" wrote in message
...
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 :)

PM time in formula...
 

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



"T. Valko" wrote:

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

"NWO" wrote in message
...
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 :)

PM time in formula...
 

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 :)




"NWO" wrote:

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



"T. Valko" wrote:

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

"NWO" wrote in message
...
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 :)

PM time in formula...
 

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

"NWO" wrote in message
...
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



"T. Valko" wrote:

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

"NWO" wrote in message
...
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 :)

PM time in formula...
 

Works fine on my computer


"NWO" wrote:

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 :)

PM time in formula...
 

In article ,
"T. Valko" wrote:

You don't need to use an array formula to do this.

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.

PM time in formula...
 

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 :)

"T. Valko" wrote:

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

"NWO" wrote in message
...
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



"T. Valko" wrote:

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

"NWO" wrote in message
...
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 :)

PM time in formula...
 

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

"NWO" wrote in message
...
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 :)

"T. Valko" wrote:

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

"NWO" wrote in message
...
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



"T. Valko" wrote:

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

"NWO" wrote in message
...
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 :)