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
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*")) |
#4
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*")) |
#5
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*")) |
#6
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*")) |
#7
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*")) |
#8
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*")) |
#9
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*")) |
#10
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*")) |
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 |