Drop time in date/time field

[Gail]

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

 

[Biff]

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$62>DATE(2005,1,1)))

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

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

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