I have 2 tables in excel. The first table has a date (date plus time stamp) and the number of good parts produced. There are numerous entries per day. On a second table I am trying to determine how many good parts per day. I am trying to do a sumif by date. The problem that is arising is the difference in dates... the first table has the timestamp and the second just has the date. Any help would be wonderful!!! Thanks in advanced!

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:30PMin A1, you would use this to extract the "date only" serial number:=DATEVALUE(TEXT(A1,"mm/dd/yyyy"))

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:

=SUMPRODUCT(--(DATEVALUE(TEXT($A$1:$A$5,"mm/dd/yyyy"))=DATEVALUE(TEXT(C1,"mm/dd/yyyy"))*$B$1:$B$5))

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Here's a shorter version that uses the =INT() function to get just the integer portion of the date, which is what is needed: =SUMPRODUCT(--(INT($A$1:$A$5)=INT(C1))*$B$1:$B$5)

MIKE

Hmm...I tried something with INT but it didn't work so I went with DATEVALUE. Now I can't duplicate what I tried so I can't figure out what I did wrong. Looks like it was case of a silly mistake leading me down the wrong path.

I definitely prefer your solution over mine.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you both! I used the INT version and the formula provided me with the correct calculations!

message edited by Kimmer215

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History