Excel CountIF по дате и времени

Я вижу множество примеров того, как работать со значениями времени ИЛИ с датами, но, похоже, мало что делается со значениями даты И времени.

У меня есть следующие данные:

Данные, например

И хотелось бы, чтобы сумма некоторых весов между определенной датой и временем (шаблоны смены):

So

  1. Мне нужны все веса между "17.07.2014 06:00" и "17.07.2014 14:00"
  2. Мне нужны все веса между "17.07.2014 14:00" и "17.07.2014 22:00"
  3. Мне нужны все веса между "17/07/2014 22:00" и "18/07/2014 06:00"

Да, я могу разделить даты и время и работать с ними отдельно, но должен быть более простой способ (это становится довольно запутанным в 3-м сценарии)

Кстати, дата для сравнения будет рассчитана с использованием «=ДАТА(ГОД(СЕЙЧАС()),МЕСЯЦ(СЕЙЧАС()),ДЕНЬ(СЕЙЧАС())-1)» для вчерашней даты (хранится в F11)

У меня есть целая страница с различными вариантами попыток, которые я не буду публиковать все, но в основном:

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

должен дать мне все после вчерашних 6 утра, но я совершенно приседаю.


excel datetime sumifs
person user1830285    schedule 18.07.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Очень близко. Excel хранит даты и время в виде дней и долей дней с 01.01.1900 или 01.01.1904. Вам нужно ДОБАВИТЬ время к дате, а не конкатенировать. И вы можете упростить получение части Date из F11. Пытаться:

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

Вы также можете жестко запрограммировать вчера в 6 утра, заменив

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

с

TODAY()-0.75

что то же самое, что:

TODAY() - 1 + TIME(6,0,0)
person Ron Rosenfeld    schedule 18.07.2014
comment
Это выглядит многообещающе, однако пока не работает. Я подозреваю, что проблема с форматом данных. Мой исходный столбец имеет пользовательский формат дд/мм/гггг чч:мм и является результатом SQL-запроса (хранится в виде строки). Есть ли способ привести элементы в диапазоне, чтобы принудительно использовать формат даты и времени? Должен ли я вообще должен? - person user1830285; 18.07.2014
comment
Я пробовал =COUNTIF(Last36Hours!$Q$2:$Q$10000,›= & TODAY() -0,75) безрезультатно - person user1830285; 18.07.2014
comment
И прежде чем кто-либо спросит, диапазон правильный (более ранние были недостаточно хороши для демонстрации) - person user1830285; 18.07.2014
comment
@user1830285 user1830285 Действительно ли данные в столбце T являются датой/временем? Если это так, =isnumber(T3) должен --› TRUE. - person Ron Rosenfeld; 18.07.2014
comment
Он вернул ложь, но формат ячейки определенно пользовательский дд/мм/гггг чч:мм. Я также попытался изменить формат ячейки на число, и он все равно вернул false. Я вернусь к моему SQL - person user1830285; 22.07.2014
comment
@user1830285 user1830285 Поскольку это ISTEXT, Excel не увидит его как дату (и изменение формата номера ячейки этого не изменит). Каковы ваши региональные настройки Windows (в панели управления)? Они должны быть такими же, как формат, исходящий из вашего SQL. Если они не совпадают, вы должны изменить один или другой. - person Ron Rosenfeld; 22.07.2014
comment
Как только я заставил формат даты/времени в SQL-запросе, это начало работать. Спасибо за вашу помощь. - person user1830285; 22.07.2014