Using DateTime when referencing a cell with SUMIFS()

Question

I am using the following SUMIFS() function:

=SUMIFS(A:A, B:B, C1)

Column B:B contains dates in the following format:

2014-01-01 01:14:44

Now in cell C1 I'm not sure how to format the date so that it will grab all records that match all records that fit what's in cell C1. I have the following:

2014-01-01 

but it return nothing. How do it make it so that it grabs the date, but doesn't discriminate the time portion?

Answer 1

You can leave C1 as a date and the data as it is - just change your formula to the following

=SUMIFS(A:A,B:B,">="&C1,B:B,"<"&C1+1)

Excel stores dates as integers, times are simply fractions of the day so every C1 date (no matter what time) falls somewhere between C1 and C1+1. This formula gets that data