Drop time in date/time field
 

I am trying to do a formula comparing a date field to 01/01/2005. It works
ok, except when the date field also has time in it. I cannot figure out how
to get rid of the time in the date/time field.

What I ultimately want to do is count all the fields in a column that have a
date (the field is null otherwise):
=SUM(IF(H$3:H$62"1/1/2005",1,0))

thanks

Hi!

It shouldn't matter if some cells have both a date and
time.

If you're comparing:

1/1/2005

1/1/2005 8:00 AM

Cells containing just dates have a true underlying value
that is an integer. Cells containing both a date and time
have a true underlying value that is a decimal:

1/1/2005 = 38353

1/1/2005 8:00 AM = 38353.3333333333333

Excel stores times as a fraction of a day and dates are
stored as integer offsets from a base date of 1/0/1900:

So, 1/1/2005 is the 38353rd day since 1/0/1900.

So, your formula should be:

=SUMPRODUCT(--(H$3:H$62DATE(2005,1,1)))

OR, use a cell to hold the date 1/1/2005:

=SUMPRODUCT(--(H$3:H$62A1))

If you simply want to count how many cells in the range
have a date, since dates are just formatted numbers:

=COUNT(H$3:H$62)

Biff

-----Original Message-----
I am trying to do a formula comparing a date field to
01/01/2005. It works
ok, except when the date field also has time in it. I
cannot figure out how
to get rid of the time in the date/time field.

What I ultimately want to do is count all the fields in a
column that have a
date (the field is null otherwise):
=SUM(IF(H$3:H$62"1/1/2005",1,0))

thanks

.