Excel Formula SUMIFS on date and time using date from given cell

Question

Excel 2016 (office 365, current version)

I have a formula that works, but is manually intensive (i.e. manually edit the date in two places for each formula, and I have to do this for an entire quarter's worth of data):

=SUMIFS('Raw'!$E:$E,'Raw'!$A:$A,">=3/30/2017 0:00",'Raw'!$A:$A,"<=3/30/2017 23:00")
yields: 35.69658611 (Correct, actual sample data below)

I would like to be able to take the date value for the local sheet column M (list of dates for the month) and plug that date directly into the formula so I can copy paste the formula to calculate the rest of the month.

What I have returns a value of zero:

=SUMIFS('Raw'!$E:$E,'Raw'!$A:$A,">="&M3 &" 0:00",'Raw'!$A:$A,"<="&M3 &" 23:00")

DataTable: Raw

Date/Time value: 'Raw' column A

Data to sum: 'Raw' Column E

Both formulas are based on hint from https://www.mrexcel.com/forum/excel-questions/601474-sumifs-date-range-criteria.html

Sample data:

DateTime        kW
3/30/17 23:00   0
3/30/17 22:00   0
3/30/17 21:00   0
3/30/17 20:00   0
3/30/17 19:00   0.004455278
3/30/17 18:00   0.5370675
3/30/17 17:00   2.303020833
3/30/17 16:00   4.122186389
3/30/17 15:00   5.415064722
3/30/17 14:00   6.190184167
3/30/17 13:00   3.621349167
3/30/17 12:00   3.292333056
3/30/17 11:00   4.470871944
3/30/17 10:00   2.407315556
3/30/17 9:00    2.269564167
3/30/17 8:00    1.033854722
3/30/17 7:00    0.029317778
3/30/17 6:00    0
3/30/17 5:00    0.000000556
3/30/17 4:00    0
3/30/17 3:00    0
3/30/17 2:00    0
3/30/17 1:00    0.000000278
3/30/17 0:00    0

as soon as I hit enter, excel reformats it to 3/30/2017 and the result is still 0 — Gavin142
note: I didn't catch it before, but the sample data is actually a 4 digit year (i.e. 3/30/2017 23:00), not a 2 digit year, don't know how/why it reformatted when I copied it out earlier. — Gavin142

MrDogme MrDogme · Accepted Answer · 2017-05-12T21:08:48

You must remember how the date format work in Excel.
Every day since 1900/01/00 (arbitrary chosen date) are indexed.
So in short, when you type 2017/03/30 in cell M3 excel really only read "42824".

=SUMIFS('Raw'!$E:$E,'Raw'!$A:$A,">="&$M3 &" 0:00",'Raw'!$A:$A,"<="&$M3 &" 23:00")

Only change required is that you must save date in Text format (i.e, prepend an apostrophe (') to the date, thereby not allowing excel to auto-format as Date).

Excel Formula SUMIFS on date and time using date from given cell

2 Answers