☰

Определение того, находится ли указанное время в пределах 15 минут от целевого времени в Excel

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Определение того, находится ли указанное время в пределах 15 минут от целевого времени в Excel

Я пытаюсь создать электронную таблицу для мониторинга своевременности выполнения резервного копирования. Резервное копирование должно выполняться в течение 15 минут от целевого времени, чтобы считаться соответствующим. Необработанные данные, загруженные из приложения онлайн-мониторинга, имеют следующие соответствующие столбцы:

  • Дата транзакции
  • Лента Этикетка
  • Время запланировано
  • Время исполнения

Я добавил в него следующие дополнительные столбцы:

  • Соответствует (Да/Нет)

    =ЕСЛИ(И(ВРЕМЯ(ЧАС(D3), МИНУТА(D3), СЕКУНДА(D3))>F3,ВРЕМЯ(ЧАС(D3), МИНУТА(D3), СЕКУНДА(D3))<G3),"Да","Нет")

  • -15 мин(Запланированное время минус 15 минут)

    =ЕСЛИ(ИЛИ(C3=ВРЕМЯ(0,0,0),C3=ВРЕМЯ(0,1,0)),ВРЕМЯ(23,59,0),C3)-ВРЕМЯ(0,15,0)+ЕСЛИ(C3=ВРЕМЯ(0,0,0),ВРЕМЯ(0,1,0),ВРЕМЯ(0,0,0))+ЕСЛИ(C3=ВРЕМЯ(0,1,0),ВРЕМЯ(0,2,0),ВРЕМЯ(0,0,0))

  • +15мин(Время по расписанию плюс 15 минут)

    =С3+"0:15"

Во всех столбцах времени используется формат времени «13:30».

Это почти работает, но есть проблема, когда Time Executed равно «0.01», а Time Scheduled равно «0:00». Это помечается как несоответствующее, хотя соответствует. Пожалуйста, может ли кто-нибудь помочь в этом?

Пример файла

Я использую MS Office 2013.

ОБНОВЛЯТЬ

Другие критерии:

  • «Время расписания» — это время выполнения резервного копирования в любой день его запуска, поэтому оно не может включать дату, поскольку справедливо для всех дат.
  • Резервное копирование иногда выполняется раньше запланированного времени. Это нормально, если это происходит в пределах 15 минут от запланированного времени.

Я понял, что мне нужно немного изменить свой лист, чтобы он выглядел следующим образом, с датами месяца, простирающимися вправо, как показано ниже, и я также удалю дату транзакции, поскольку транзакция будет отображаться под соответствующей датой. Однако, пожалуйста, используйте вышеизложенное в качестве примеров (изображение ниже предоставлено для того, чтобы помочь понять, что нам нужно учесть).

введите описание изображения здесь

решение1

Это можно сделать так:

=IF(OR(ABS(D3-INT(D3)-(C3-INT(C3)))<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))-1)<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))+1)<TIME(0,15,0)),"Yes","No")

OR существует для проверки того, относится ли фактическое время к дню до или после. Он необходим для работы с этими пограничными случаями.

Обратите внимание, что это C3+1означает любое время в C3 + 1 день. Это работает, потому что Excel хранит дату и время одинаково, как количество дней с 1/1/1900. Десятичные знаки на самом деле представляют собой время, хранящееся как часть дня. Если вы введете какое-то время, скажем, 12:00, оно будет сохранено как число 0,5. То же время 1/2/1900 будет 2,5.

Однако это может привести к ложным срабатываниям, если фактическое резервное копирование будет выполнено слишком поздно или слишком рано в период между 23:45 и 24:15.

решение2

В моем примере столбцы A–D содержат те же данные, что и в вашем примере. Столбцы E, F и G затем заполняются формулами, указанными ниже.

Вы можете добавить два столбца с отметкой даты/времени. Один для времени расписания, которое в вашем случае (столбец E):

=A2+C2

И один — время начала (столбец F).

=IF(D2<C2-TIME(0,15,0),A2+1+D2,A2+D2)

Эта формула проверяет, меньше ли время начала, чем время в расписании минус 15 минут (то есть на следующий день, а если оно на 15 минут раньше, то в любом случае это не соответствует требованиям), а затем добавляет один день к отметке даты времени.

Затем вы можете просто проверить, соответствует ли это этой формуле (столбец G):

IF(AND(F2<E2+TIME(0,15,0),F2>=E2),"Compliant","Not compliant")

Редактировать: Прочитав все ваши комментарии ранее, время начала и запланированное время являются объектами даты и времени, поэтому что-то простое вроде этого должно сработать.

=IF(AND(D2>C2-TIME(0,15,0),D2<C2+TIME(0,15,0)),"Compliant","Non compliant")

решение3

Я думаю, что это невозможно сделать с тем, как вы сейчас настроили. Используя только функции TIME, Excel не может отличить вчерашний день от сегодняшнего. В фоновом режиме TIME изменяется от 0 в полночь до .99999999 в 23:59:59. Насколько мне известно, оно не переходит на 1.0000, 1.0001 и т. д.

Если вы можете включить в свой отчет полную временную метку, то вашу формулу можно скорректировать, чтобы понять, что 15 минут назад могли фактически быть частью вчерашнего дня, а 15 минут спустя могли быть частью будущей даты.

решение4

вам следует учитывать столбец даты.

A: Дата транзакции
C: Расписание
D: Время начала
E: Соответствует

-15 Min column (F):  =A6+C6-TIME(0,15,0)  
+15 Min column (G):  =A6+C6+TIME(0,15,0)

Compliant column (E):   =--AND(A6+D6>=F6,A6+D6<=G6)

Результат для соответствия: (0 = Нет, 1 = Да)

Вы также можете сделать это без столбца -/+ 15 мин:

=--AND(A6+D6>=A6+C6-TIME(0,15,0),A6+D6<=A6+C6+TIME(0,15,0))

Связанный контент