|As you may know, Excel stores Dates and Time as serial numbers. The integer portion of the serial number is the date and the decimal portion is the time. Therefore a cell with a date and a time will not be equal to a cell with just a date since the internal serial numbers will be different.|
One way to extract just the date portion of a date and time is to use the DATEVALUE function, which requires a TEXT version of the date:
With 1/1/2014 11:30PM in A1, you would use this to extract the "date only" serial number:
This should return 41640, the serial number for the date only. If a cell contains 1/1/2014 without the time stamp, that formula will also return 41640 since it is only returning the information related to the date.
Now, it would be nice if you could use the DATEVALUE function as the criteria in a SUMIF function, testing to see if the DATEVALUE of a cell with a time stamp is the same as the DATEVALUE of a cell without a time stamp, but unfortunately the SUMIF function can't handle that. However, the SUMPRODUCT function can.
With dates and timestamps in A1:A5 and dates only in C1:C5, you can "sum if" B1:B5 with this SUMPRODUCT formula:
Let us know how that works out for you.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.