Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unmet challenge | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
Can you insert a vertical scrolling field in excel? Not a drop dow | Excel Worksheet Functions | |||
Pivot Tables..I give up... | Excel Worksheet Functions | |||
Filtering Data with a Drop Down field | Excel Worksheet Functions |