Sumproduct Date clarification

[Walter Mayes]

In one of my spreadsheets I have a sumproduct formula that looks down a
column of dates and returns 7 day totals for certain items. Works great. The
formula is, in part:
=sumproduct(((meter readings!a$4:$a$1064>p$4).........

Column A contains =b4. Both columns, A and B, are formatted as dates: I.E.
04/03/06 P4 is also formatted as a date I.E. 3-Apr-06

In another spreadsheet I was setting up a formula to get quarterly
totals. Same basic idea as the above formula. My formula is/was, in part:

=sumproduct(((register01532!$b$7:$b$401>=1/1/06)...

Column B formatted as dates. This would not work. Returned nothing but 0's.
By modifying the formula to .....>=date(2006,1,1)).... it worked.

Question is: Why did I have to use the Date function in one formula and
not the other?

Walter Mayes

 

[Biff]

Hi!

Question is: Why did I have to use the Date function in one formula and not
the other?

You didn't have to use the Date function. In the one formula you're
referencing a cell that holds the date.

In the below formula:

=sumproduct(((register01532!$b$7:$b$401>=1/1/06)...

You have what *YOU* think is a date but Excel sees 1 divided by 1 divided by
6.

Try it this way:

=sumproduct(((register01532!$b$7:$b$401>=--"1/1/2006")

Biff

 

[Ken Johnson]

Hi Walter,
I think the reason is that when a cell is formatted as date it displays
the date but the real value is hidden behind the scenes and is a value
depending on the computer's date system.
For example, say A1 is formatted as Date and B1 is formatted as Number.
If you then type say 2/3/06 into A1 you will probably see (depending on
the details of the date format chosen) 2/03/2006. Now, if you type into
B1 the formula =A1 you will probably see; depending on the date system
used by your computer, 1900 or 1904; 38778.00 or 37316.00 respectively.
So, in the formula that was using p$4 excel was using the hidden value
to do the calculation.
In your other formula using 1/6/06, excel has calculated 1/6/06
algebraically as 1/6/6 = 1/36 = 0.03. For date calculations this
corresponds to just 3 hundredths of the first day of the starting day
of your computer's date system 1/1/1900 or 1/1/1904 hence, nothing but
0's. The use of date(2006,1,1) has corrected that error.

Ken Johnson

 

[Bob Phillips]

Mr Biff,

Love to see you coercing a date with the unary operator, but could I suggest
that you use the form

=SUMPRODUCT(((register01532!$b$7:$b$401>=--"2006-01-01")

it removes all ambiguites with dates re mm/dd or dd/mm formats.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Walter Mayes]

Thanks Biff, Bob and Ken

I had tried various forms of the date in my formula but did not try the
quotes. I was assuming that when a cell was formatted as a date, Excel would
automatically see 1/1/06 etc. as a number. "DUH, The light bulb just
went on". In a cell formatted as a date, Excel sees a number BUT
1/1/06 in a formula is a far different story. Maybe a good nights sleep DOES
make a difference.

Thanks Again
Walter Mayes

 

[Biff]

That's a good point, Bob.

I myself would not use that expression in a formula. I just don't like the
way it looks! I would use a cell to hold the date or use the Date function
in the formula.

I was just showing the OP how it could be done using the date string.

Biff

 

[Bob Phillips]

I agree, just on a crusade to get Americans to think worldwide <bg>

Bob

 

[Dave Peterson]

From what I read in the newspapers, that isn't always a good thing <vvbg>.