DCOUNT and Dates

[tyemm]

A simple question, that I could not find addressed in MS Help or in Que:

What is the proper syntax to do a count of dates in a column/range which fulfill a criterion such as >01/01/2001 or between 01/01/2001 and 12/31/2001?

If the answer involves serial date formatting, I can deal with that!
Thanks
Tom

 

[SkipVought]

Hi,

It's not a matter of formatting, it is a matter of Date Values, which are NUMBERS.

Either enter the date value in a CELL and reference the CELL in your DCOUNT or use the DATE function to convert Year, Month and Day values to a Date Value or DATEVALUE to convert a date string to a Date Value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Do you need to use DCount? I ask because I would tend to just use CountIf.

If your dates are in column A, then you could use:
[tab][COLOR=blue white]=Countif(A:A, ">" & MinDate) - Countif(A:A, ">" & MaxDate)[/color]
where MinDate and MaxDate are named ranges. If you want to hardcode the dates into the formula it would look like:
[tab][COLOR=blue white]=Countif(A:A, ">" & datevalue("01/01/2008")) - Countif(A:A, ">" & datevalue("02/01/2008"))[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[tyemm]

Thank you for the reply--
Here is my cell text, accessing a range of date values in an adjacent worksheet:
=COUNTIF('Processed since Go Live'!B2:B1436,">" & DATEVALUE("09/01/2007"))

Why am I getting zero, or value! when I can see that I have some 40-odd date values in that column range of dates?

 

[SkipVought]

1) are the values in column B, REAL DATES? Change the Format to GENERAL on the column to verify.

2) You have chosen an AMBIGUOUS date string format. That is not necessarily a problem but it could be.

Is it mm/dd/yyyy or dd/mm/yyyy. Microsoft assumes the FORMER.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Almost certainly text masquerading as dates....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tyemm]

I have verified real dates, and I am using the assumed date format (the other, dd/mm/yyyy would still give me a non-zero answer).
=COUNTIF('Processed since Go Live'!B2:B1436,">" & DATEVALUE("09/01/2007"))
is returning zero when it should count about 40 dates. Originally I thought I needed DCOUNT or COUNTA, but really, isn't this simple?

 

[xlbo]

how have you verified real dates?

What happens to them if you cahnage the format to general?

Your formula should work if you have real dates in B2:B1436



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluedragon2]

Use the function wizard and in the range section, are you getting 5 digit numbers in your set?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

FYI,

I dummied up some data with REAL DATES in column B on sheet 'Processed since Go Live' ...

and got COUNT from =COUNTIF('Processed since Go Live'!B2:B1436,">" & DATEVALUE("09/01/2007"))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Have you CALCULATED? F9.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tyemm]

I am, when I run the datevalue parameter. So my question is, if my dates are text, how best to convert; they came from csv values I believe?

 

[tyemm]

You were correct!
My data had come in as text, apparently. So Formatting in Excel as dates created the illusion of dates, when from a calculating standpoint, it was still text!
I'm better now...

 

[SkipVought]

How did you get the data?

Did you OPEN a TEXT file or did you IMPORT a TEXT file. With Data > Import... you can parse the data and perform a date conversion.

Otherwise, use the DATEVALUE to convert column B in, for instance, column C. Then Column C would have REAL DATES.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]