Search code examples
exceldatetimesumifs

Excel CountIF on date AND time

I see lots of examples of how to work with times OR dates, but not a lot seems to be done with date AND time values.

I have the following data:

Data e.g.

And would like the sum of some weights between specific date and times (shift patterns):

So

  1. I need all the weights between "17/07/2014 06:00" and "17/07/2014 14:00"
  2. I need all the weights between "17/07/2014 14:00" and "17/07/2014 22:00"
  3. I need all the weights between "17/07/2014 22:00" and "18/07/2014 06:00"

Yes, I can split the dates and times and deal with them separately, but there must be an easier way (it gets rather convoluted in the 3rd scenario)

By the way, the date to compare against will be calculated using "=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())-1)" for yesterdays date (stored in F11)

I have an entire page of different variations of attempts at this, which I wont post all of, but mainly:

 =SUMIFS(
   Last36Hours!$K$2:$K$10000,
   Last36Hours!$T$2:$T$10000,">=" & 
      DATE(YEAR(F11),MONTH(F11),DAY(F11)) & 
      TIME(6,0,0)
 )

should give me everything after yesterday 6am, but i get absolutely squat.

Solution

  • Very Close. Excel stores dates and times as days and fractions of days since 1/1/1900 or 1/1/1904. You need to ADD the time to the date, not concatenate. And you can simplify getting the Date portion from F11. Try:

    =SUMIFS(
   Last36Hours!$K$2:$K$10000,
   Last36Hours!$T$2:$T$10000,">=" &
     INT(F11)+ TIME(6,0,0))

    You could also hard code yesterday at 6AM by replacing 

    DATE(YEAR(F11),MONTH(F11),DAY(F11)) & TIME(6,0,0)

    with

    TODAY()-0.75

    which is the same as:

    TODAY() - 1 + TIME(6,0,0)