Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
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$1064p$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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
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 "Walter Mayes" wrote in message ... 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$1064p$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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
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) "Biff" wrote in message ... 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 "Walter Mayes" wrote in message ... 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$1064p$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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
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 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 "Walter Mayes" wrote in message ... 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$1064p$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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
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" wrote in message ... 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) "Biff" wrote in message ... 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 "Walter Mayes" wrote in message ... 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$1064p$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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
I agree, just on a crusade to get Americans to think worldwide <bg
Bob "Biff" wrote in message ... 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" wrote in message ... 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) "Biff" wrote in message ... 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 "Walter Mayes" wrote in message ... 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$1064p$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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Date clarification
From what I read in the newspapers, that isn't always a good thing <vvbg.
Bob Phillips wrote: I agree, just on a crusade to get Americans to think worldwide <bg Bob "Biff" wrote in message ... 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" wrote in message ... 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) "Biff" wrote in message ... 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 "Walter Mayes" wrote in message ... 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$1064p$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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct by date | Excel Discussion (Misc queries) | |||
average value from a table | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Using sumproduct to count number by date | Excel Worksheet Functions |