| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare/Find Exact Date match with varying time
I have a column that is formatted with a date and timestamp
10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
I got a little closer using COUNT:
=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) But that yielded a false answer as well. I got 2 when hitting <ENTER and 1998 if I did the CSE (ctrl / shift / enter) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
Try this:
=SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007")) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
Getting:
#N/A using that one. "Teethless mama" wrote: Try this: =SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007")) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
Assuming that your cells are not storing the dates and times as text
strings, but as Excel date & time values, then you'll need to convert and process the data. One option for your condition might be along the lines of either INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of date representation) INT(cell_ref)=DATE(2007,4,9) Another option might be something like TEXT(cell_ref,"d/m/yyyy")="9/4/2007" -- David Biddulph "sweens319" wrote in message ... I got a little closer using COUNT: =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) But that yielded a false answer as well. I got 2 when hitting <ENTER and 1998 if I did the CSE (ctrl / shift / enter) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
Put quotes around the "IDX"
=SUMPRODUCT(--(CSBD!K2:K1000="IDX"),--(INT(CSBD!$H2:H1000)=--"9/2/2007")) "sweens319" wrote: Getting: #N/A using that one. "Teethless mama" wrote: Try this: =SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007")) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
I'm not really sure how it's stored. The cells are formatted as "Custom"
m/d/yyy h:mm Perhaps I am just dumb, but I can't seem to figure out how to incorporate your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula. =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) or =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) "David Biddulph" wrote: Assuming that your cells are not storing the dates and times as text strings, but as Excel date & time values, then you'll need to convert and process the data. One option for your condition might be along the lines of either INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of date representation) INT(cell_ref)=DATE(2007,4,9) Another option might be something like TEXT(cell_ref,"d/m/yyyy")="9/4/2007" -- David Biddulph "sweens319" wrote in message ... I got a little closer using COUNT: =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) But that yielded a false answer as well. I got 2 when hitting <ENTER and 1998 if I did the CSE (ctrl / shift / enter) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
Could the answer be a combination of both of these attempts (David &
Teethless)? I don't think putting quotes around IDX is correct, because IDX is a named array. But, then again, I could be wrong, because without the quotes I get #N/A for the result, and with the quotes I get $0 (14 is the correct answer). Thank you for your continued help. "Teethless mama" wrote: Put quotes around the "IDX" =SUMPRODUCT(--(CSBD!K2:K1000="IDX"),--(INT(CSBD!$H2:H1000)=--"9/2/2007")) "sweens319" wrote: Getting: #N/A using that one. "Teethless mama" wrote: Try this: =SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007")) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
To get all values with the same date use something like
=SUMPRODUCT(--(INT(CSBD!H2:H1000)=DATE(2007,4,9)),--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0)))) -- Regards, Peo Sjoblom "sweens319" wrote in message ... I'm not really sure how it's stored. The cells are formatted as "Custom" m/d/yyy h:mm Perhaps I am just dumb, but I can't seem to figure out how to incorporate your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula. =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) or =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) "David Biddulph" wrote: Assuming that your cells are not storing the dates and times as text strings, but as Excel date & time values, then you'll need to convert and process the data. One option for your condition might be along the lines of either INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of date representation) INT(cell_ref)=DATE(2007,4,9) Another option might be something like TEXT(cell_ref,"d/m/yyyy")="9/4/2007" -- David Biddulph "sweens319" wrote in message ... I got a little closer using COUNT: =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) But that yielded a false answer as well. I got 2 when hitting <ENTER and 1998 if I did the CSE (ctrl / shift / enter) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
Thank you all so much for all your help.
This worked perfectly except I reformatted my column to mm/dd/yyyy hh:mm and then changed DATE(2007,4,9) -- below to DATEVALUE("09/07/2007") Before reformatting and changing this one little piece, it still resulted in 0 for some reason. Perhaps the format was just odd. Thanks Again... "Peo Sjoblom" wrote: To get all values with the same date use something like =SUMPRODUCT(--(INT(CSBD!H2:H1000)=DATE(2007,4,9)),--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0)))) -- Regards, Peo Sjoblom "sweens319" wrote in message ... I'm not really sure how it's stored. The cells are formatted as "Custom" m/d/yyy h:mm Perhaps I am just dumb, but I can't seem to figure out how to incorporate your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula. =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) or =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) "David Biddulph" wrote: Assuming that your cells are not storing the dates and times as text strings, but as Excel date & time values, then you'll need to convert and process the data. One option for your condition might be along the lines of either INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of date representation) INT(cell_ref)=DATE(2007,4,9) Another option might be something like TEXT(cell_ref,"d/m/yyyy")="9/4/2007" -- David Biddulph "sweens319" wrote in message ... I got a little closer using COUNT: =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) But that yielded a false answer as well. I got 2 when hitting <ENTER and 1998 if I did the CSE (ctrl / shift / enter) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare/Find Exact Date match with varying time
Well, of course, the two dates are different. The first is 9th April 2007,
whereas the second is either 9th July 2007 or 7th September 2007, depending on your Windows Regional Settings. It is this latter ambiguity that leads to the recommendation to use the DATE function, rather than DATEVALUE. -- David Biddulph "sweens319" wrote in message ... Thank you all so much for all your help. This worked perfectly except I reformatted my column to mm/dd/yyyy hh:mm and then changed DATE(2007,4,9) -- below to DATEVALUE("09/07/2007") Before reformatting and changing this one little piece, it still resulted in 0 for some reason. Perhaps the format was just odd. Thanks Again... "Peo Sjoblom" wrote: To get all values with the same date use something like =SUMPRODUCT(--(INT(CSBD!H2:H1000)=DATE(2007,4,9)),--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0)))) "sweens319" wrote in message ... I'm not really sure how it's stored. The cells are formatted as "Custom" m/d/yyy h:mm Perhaps I am just dumb, but I can't seem to figure out how to incorporate your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula. =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) or =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) "David Biddulph" wrote: Assuming that your cells are not storing the dates and times as text strings, but as Excel date & time values, then you'll need to convert and process the data. One option for your condition might be along the lines of either INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of date representation) INT(cell_ref)=DATE(2007,4,9) Another option might be something like TEXT(cell_ref,"d/m/yyyy")="9/4/2007" -- David Biddulph "sweens319" wrote in message ... I got a little closer using COUNT: =COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*")) But that yielded a false answer as well. I got 2 when hitting <ENTER and 1998 if I did the CSE (ctrl / shift / enter) "sweens319" wrote: I have a column that is formatted with a date and timestamp 10/4/2007 14:21 I want to find all the files that were completed on a specific date at varying times. I searched the help and found a little on criteria using wildcards, but I can't seem to figure out the correct way to write my formula. I tried the following and keep getting a result of 0 (manual calculation proves the correct answer is 0): $H2:H1000="9/2/2007*" - 0 $H2:H1000="9/2/2007"* - error $H2:H1000=9/2/2007* - error Perhaps my whole formula is incorrect somehow. I've got multiple criteria, one being matching a separate columns value to an array (thank you to the answer-people on my previous posts for making this possible for me). =SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*")) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find exact match, same workbook, different sheets | Excel Discussion (Misc queries) | |||
| verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
| Find a not exact match using vlookup | Excel Discussion (Misc queries) | |||
| using vlookup to find exact match | Excel Discussion (Misc queries) | |||
| Find a match that;s not exact | Excel Worksheet Functions | |||
Linear Mode
