Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
Can the criteria for a database function be in the form of a Date (e.g.
1/1/07). If so how? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
If you mean greater than 1/1/2007?
="2007-01-01" or =""&TEXT(DATE(2007,1,1),"yyyy-mm-dd") or =""&DATE(2007,1,1) or ="1/1/07" I would advice against the latter 2, the first of those will display the serial number of the date and the latter will fail if moved to another regional setting -- Regards, Peo Sjoblom "jrv" wrote in message ... Can the criteria for a database function be in the form of a Date (e.g. 1/1/07). If so how? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
Yes I did mean greater than 1/1/2007. However, It doesn't seem to work.
Here's what I did to check. I created a simple database with "Date" as the first column and "Check" As the second column (= to all 1s). I populated dates in the date column in the syntax mm/dd/yy. I then established a criteria array with one column two rows, First row is labeled Date. In the second row I typed the criteria as you show below (i.e. ="2007-01-01"). When I hit enter the cell shows 2007-01-01. However, the cell with the Database Function shows the #VALUE! error. Any suggestions? "Peo Sjoblom" wrote: If you mean greater than 1/1/2007? ="2007-01-01" or =""&TEXT(DATE(2007,1,1),"yyyy-mm-dd") or =""&DATE(2007,1,1) or ="1/1/07" I would advice against the latter 2, the first of those will display the serial number of the date and the latter will fail if moved to another regional setting -- Regards, Peo Sjoblom "jrv" wrote in message ... Can the criteria for a database function be in the form of a Date (e.g. 1/1/07). If so how? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
I fixed the #Value! error. However, DSUM is returning 0 on check eventhough
there are dates that meet the criteria. "jrv" wrote: Yes I did mean greater than 1/1/2007. However, It doesn't seem to work. Here's what I did to check. I created a simple database with "Date" as the first column and "Check" As the second column (= to all 1s). I populated dates in the date column in the syntax mm/dd/yy. I then established a criteria array with one column two rows, First row is labeled Date. In the second row I typed the criteria as you show below (i.e. ="2007-01-01"). When I hit enter the cell shows 2007-01-01. However, the cell with the Database Function shows the #VALUE! error. Any suggestions? "Peo Sjoblom" wrote: If you mean greater than 1/1/2007? ="2007-01-01" or =""&TEXT(DATE(2007,1,1),"yyyy-mm-dd") or =""&DATE(2007,1,1) or ="1/1/07" I would advice against the latter 2, the first of those will display the serial number of the date and the latter will fail if moved to another regional setting -- Regards, Peo Sjoblom "jrv" wrote in message ... Can the criteria for a database function be in the form of a Date (e.g. 1/1/07). If so how? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
What's the formula you are using, it certainly works for me if I create the
same, assuming you want to sum all checks with dates greater than 01/01/07 =DSUM(DataBase,"Check",E1:E2) or to count the dates =DCOUNT(DataBase,"Dates",E1:E2) the D functions don't work if the source workbook is closed if you have the formula in another workbook -- Regards, Peo Sjoblom "jrv" wrote in message ... Yes I did mean greater than 1/1/2007. However, It doesn't seem to work. Here's what I did to check. I created a simple database with "Date" as the first column and "Check" As the second column (= to all 1s). I populated dates in the date column in the syntax mm/dd/yy. I then established a criteria array with one column two rows, First row is labeled Date. In the second row I typed the criteria as you show below (i.e. ="2007-01-01"). When I hit enter the cell shows 2007-01-01. However, the cell with the Database Function shows the #VALUE! error. Any suggestions? "Peo Sjoblom" wrote: If you mean greater than 1/1/2007? ="2007-01-01" or =""&TEXT(DATE(2007,1,1),"yyyy-mm-dd") or =""&DATE(2007,1,1) or ="1/1/07" I would advice against the latter 2, the first of those will display the serial number of the date and the latter will fail if moved to another regional setting -- Regards, Peo Sjoblom "jrv" wrote in message ... Can the criteria for a database function be in the form of a Date (e.g. 1/1/07). If so how? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
Try
=ISTEXT(cell) on both the dates and the check values, if they are imported they may have trailing/leading spaces -- Regards, Peo Sjoblom "jrv" wrote in message ... I fixed the #Value! error. However, DSUM is returning 0 on check eventhough there are dates that meet the criteria. "jrv" wrote: Yes I did mean greater than 1/1/2007. However, It doesn't seem to work. Here's what I did to check. I created a simple database with "Date" as the first column and "Check" As the second column (= to all 1s). I populated dates in the date column in the syntax mm/dd/yy. I then established a criteria array with one column two rows, First row is labeled Date. In the second row I typed the criteria as you show below (i.e. ="2007-01-01"). When I hit enter the cell shows 2007-01-01. However, the cell with the Database Function shows the #VALUE! error. Any suggestions? "Peo Sjoblom" wrote: If you mean greater than 1/1/2007? ="2007-01-01" or =""&TEXT(DATE(2007,1,1),"yyyy-mm-dd") or =""&DATE(2007,1,1) or ="1/1/07" I would advice against the latter 2, the first of those will display the serial number of the date and the latter will fail if moved to another regional setting -- Regards, Peo Sjoblom "jrv" wrote in message ... Can the criteria for a database function be in the form of a Date (e.g. 1/1/07). If so how? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
I was using the DSUM formula you show below. It's just not working for me.
There must be something different. Is there anyway you can send me your "test" file so I can take a look? "Peo Sjoblom" wrote: What's the formula you are using, it certainly works for me if I create the same, assuming you want to sum all checks with dates greater than 01/01/07 =DSUM(DataBase,"Check",E1:E2) or to count the dates =DCOUNT(DataBase,"Dates",E1:E2) the D functions don't work if the source workbook is closed if you have the formula in another workbook -- Regards, Peo Sjoblom "jrv" wrote in message ... Yes I did mean greater than 1/1/2007. However, It doesn't seem to work. Here's what I did to check. I created a simple database with "Date" as the first column and "Check" As the second column (= to all 1s). I populated dates in the date column in the syntax mm/dd/yy. I then established a criteria array with one column two rows, First row is labeled Date. In the second row I typed the criteria as you show below (i.e. ="2007-01-01"). When I hit enter the cell shows 2007-01-01. However, the cell with the Database Function shows the #VALUE! error. Any suggestions? "Peo Sjoblom" wrote: If you mean greater than 1/1/2007? ="2007-01-01" or =""&TEXT(DATE(2007,1,1),"yyyy-mm-dd") or =""&DATE(2007,1,1) or ="1/1/07" I would advice against the latter 2, the first of those will display the serial number of the date and the latter will fail if moved to another regional setting -- Regards, Peo Sjoblom "jrv" wrote in message ... Can the criteria for a database function be in the form of a Date (e.g. 1/1/07). If so how? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Database Function Criteria be a Date?
I just sent you an email with a sample workbook attached
-- Regards, Peo Sjoblom "jrv" wrote in message ... Thanks a ton. jrv "Peo Sjoblom" wrote: Sure, post your email address but do it so no spambots can get a hold of it so if your email address is post it like johndoeNOSPAMatNOSPAMyahoo.com and I will send you a little sample -- Regards, Peo Sjoblom "jrv" wrote in message ... I was using the DSUM formula you show below. It's just not working for me. There must be something different. Is there anyway you can send me your "test" file so I can take a look? "Peo Sjoblom" wrote: What's the formula you are using, it certainly works for me if I create the same, assuming you want to sum all checks with dates greater than 01/01/07 =DSUM(DataBase,"Check",E1:E2) or to count the dates =DCOUNT(DataBase,"Dates",E1:E2) the D functions don't work if the source workbook is closed if you have the formula in another workbook -- Regards, Peo Sjoblom "jrv" wrote in message ... Yes I did mean greater than 1/1/2007. However, It doesn't seem to work. Here's what I did to check. I created a simple database with "Date" as the first column and "Check" As the second column (= to all 1s). I populated dates in the date column in the syntax mm/dd/yy. I then established a criteria array with one column two rows, First row is labeled Date. In the second row I typed the criteria as you show below (i.e. ="2007-01-01"). When I hit enter the cell shows 2007-01-01. However, the cell with the Database Function shows the #VALUE! error. Any suggestions? "Peo Sjoblom" wrote: If you mean greater than 1/1/2007? ="2007-01-01" or =""&TEXT(DATE(2007,1,1),"yyyy-mm-dd") or =""&DATE(2007,1,1) or ="1/1/07" I would advice against the latter 2, the first of those will display the serial number of the date and the latter will fail if moved to another regional setting -- Regards, Peo Sjoblom "jrv" wrote in message ... Can the criteria for a database function be in the form of a Date (e.g. 1/1/07). If so how? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
strings as criteria in database function DCOUNT | Excel Worksheet Functions | |||
=DMIN(database,field,criteria) question about criteria | Excel Discussion (Misc queries) | |||
Database Function Criteria Boolean Operations | Excel Worksheet Functions | |||
Database function criteria | Excel Worksheet Functions | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions |