☰

Excel에서 지정된 시간이 목표 시간의 15분 이내인지 확인

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Excel에서 지정된 시간이 목표 시간의 15분 이내인지 확인

실행된 백업의 적시성을 모니터링하기 위해 스프레드시트를 만들려고 합니다. 규정을 준수하려면 목표 시간의 15분 이내에 백업을 실행해야 합니다. 온라인 모니터링 애플리케이션에서 다운로드한 원시 데이터에는 다음과 같은 관련 열이 있습니다.

  • 거래 날짜
  • 테이프 라벨
  • 예정된 시간
  • 실행된 시간

여기에 다음과 같은 추가 열을 추가했습니다.

  • 준수(예/아니요)

    =IF(AND(TIME(HOUR(D3), MINUTE(D3), SECOND(D3))>F3,TIME(HOUR(D3), MINUTE(D3), SECOND(D3))<G3),"예", "아니요")

  • -15 분(예정시간 - 15분)

    =IF(OR(C3=시간(0,0,0),C3=시간(0,1,0)),시간(23,59,0),C3)-시간(0,15,0)+IF (C3=시간(0,0,0),시간(0,1,0),시간(0,0,0))+IF(C3=시간(0,1,0),시간(0,2, 0),시간(0,0,0))

  • +15분(예정된 시간 + 15분)

    =C3+"0:15"

모든 시간 열은 "13:30" 시간 형식을 사용합니다.

거의 작동되는데 Time Execated가 "0.01"이고 Time Scheduled가 "0:00"인 경우 문제가 발생합니다. 이는 준수하는 경우 비준수로 표시됩니다. 이 작업을 수행하는 데 누군가 도움을 줄 수 있습니까?

파일의 예

MS오피스 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")

또는 실제 시간이 전날 또는 이후를 참조하는지 테스트하기 위해 존재합니다. 이러한 한계 사례를 해결하는 데 필요합니다.

이는 C3+1C3 + 1일의 모든 시간을 의미합니다. 이는 Excel이 1900년 1월 1일의 일 수와 동일한 방식으로 날짜 및 시간을 저장하기 때문에 작동합니다. 소수는 실제로 하루의 일부로 저장된 시간입니다. 12시 등의 시간을 입력하면 숫자 0.5로 저장됩니다. 1900년 1월 2일의 같은 시간은 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분보다 빠르면 어느 쪽이든 비준수임) 시간 날짜 스탬프에 1일을 더합니다.

다음 공식(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에서 오후 11시 59분 59초에 .99999999로 변경됩니다. 내가 아는 한 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 Min 열 없이도 수행할 수 있습니다.

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

관련 정보