dBase Function criteria question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RC - 10 Oct 2007 01:25 GMT

Hello,

I'm trying to use a database function to count the number of dates
that fall within a certain range, e.g. 2/1/2007 and 2/28/2007,
inclusive.

My criteria are

Start Date

>=2/1/07

Start Date
<=2/28/07

When I use DCOUNTA, it will not count 2/1/2007 or 2/28/2007. I know
that I can use my criteria as >1/31/07 and <3/1/07, but is there a way
to get the above to work too?

Thank you for any help!

Reply to this Message

Dave Peterson - 10 Oct 2007 01:57 GMT

Untested:

>=date(2007,2,1)

and
<=date(2007,2,28)

> Hello,
>
[quoted text clipped - 15 lines]
>
> Thank you for any help!

Signature

Dave Peterson

Reply to this Message

Peo Sjoblom - 10 Oct 2007 04:48 GMT

You need to use

=">="&DATE(2007,2,1)

="<="&DATE(2007,2,28)

the problem is that when you look at the criteria it will be the serial
number so to get something that looks like a date and works one can use

=">="&TEXT(DATE(2007,2,1),"mm/dd/yy")

="<="&TEXT(DATE(2007,2,28),"mm/dd/yy")

using US date format

which means that this will work as well in the US

=">=02/01/07"

="<=02/28/07"

to work in a few more countries one can use

=">=2007-02-01"

="<=2007-02-28"

I am obviously bored <g>

Signature

Regards,

Peo Sjoblom

> Untested:
>>=date(2007,2,1)
[quoted text clipped - 20 lines]
>>
>> Thank you for any help!

Reply to this Message

RC - 11 Oct 2007 14:22 GMT

Hi, thank you for the suggestions!

What I ended up finding was that my formula was working correctly ...
but that the data that I was counting (from MS Project) had
timestamps, such as

2/28/07 8:00 AM

So Excel was not counting those because I never specified the time
for, e.g., <=2/28/07. So Excel was comparing 2/28/07 8:00AM to 2/28/07
12:00AM. So what I did was format the MS Project data so that all
dates were at 12:00AM (0:00) because the times are not important to
me, and it worked ok.

Reply to this Message

RC - 10 Oct 2007 02:22 GMT

Nevermind! Error on my part! Thanks.

Reply to this Message