Excel Dates - Comparison Operator Issue

D

dmaslen

I have a column that is a date field. So in A1, let's say there i
5/22/2004.

I am using this field to try to query a dataset that I have on anothe
sheet. What I want to do is find all values in that dataset that ar
less than or equal to my date value in A1.

If I use the following formula, the function works: <=5/22/2004

However, I want to reuse this function many times, and want to place a
absolute reference in my calculation to reuse the value in A1, i.e.
want something like <=$A$1.

Excel doesn't seem to recognize this properly because it is a dat
field, and I can't figure out a way around this.

I have tried many different things including breaking down the dat
into day, month, and year components, and then concatenating it back t
a text field.

Does anyone have any clue on how to absolutely reference a date fiel
and combine it with a comparison operator??

Any help would be appreciated.
 
P

Peo Sjoblom

It does not work, excel sees that as a tiny number 0.000113409544547269
while if you have real dates it would be 38129 for 05/22/04
You can use the cell reference, I can only assume since you say that doesn't
work
that your date must be text or something To use the date expression you have
to use

=A1<=--"5/22/2004"

however using a cell reference with a real date will work

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
J

Jason Morin

Post both functions: the one that works and the one you're
attempting to create.

HTH
Jason
Atlanta, GA
 
D

dmaslen

Okay, here is the function that works:

<=5/22/2004

This function is part of a DSUM that is referencing my dataset. It goe
through the date column in my dataset, and finds all values less tha
or equal to the May 22, 2004 date.

What I need to do is to have a fixed date in one field, say A1. So i
A1, I would put 5/22/2004, and this is a date field.

The function that doesn't work is:

<=A1. It isn't referencing the date field properly, and I don't kno
why.

Thanks,
 
P

Peo Sjoblom

Ok, I understand, if your formula would look like

=DSUM(A3:B32,"Amounts",J1:J2)

where J2 holds <=5/22/2004 and J1 holds the header
and Amounts is the column you want to SUM
There might be an expression that will work using the same style
but I know that the formula version will work, e.g.
remove the header from J1 (leave it blank), then in J2 put

=A4<=$A$1

where A1 holds the fixed date and A4 is the first cell in the date column
with a date (not a header)


--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
P

Peo Sjoblom

I don't normally open files from strangers but I made an exception this time
because I feel rather lucky today. I got the same result as the hard coded
criteria by deleting everything in D6 (leave blank)
and in D7 using this formula =A2<=$G$2
Your DSUM formula will stay the same =DSUM(Database, $B$1, D6:D7)

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Top