콜론(hhmmss) 없이 시간을 입력하고 Excel에서 시차를 계산합니다.

Question

콜론 없이 시간을 입력 hhmmss하고 다른 셀과의 시간 차이를 계산하고 싶습니다. 예를 들어 한 셀에는 시작 시간을 입력하고 다른 셀에는 종료 시간을 입력합니다. 그런 다음 시차를 계산해야 합니다. 단, 시간, 분, 초가 포함되어야 합니다.

문제는 콜론 없이 시간을 입력하고 싶지만 셀에 콜론이 있는 상태로 보고 싶다는 것입니다. 이를 위해 사용자 정의 숫자 형식으로 셀의 서식을 지정했습니다 00\:00\:00.

Answer 1

한 가지 옵션은 Excel 서식과 셀 서식을 조합하여 사용하는 것입니다.시간기능.

시간 입력 셀(예제에서는 A2 및 B2)의 형식을 Text. 예상되는 형식은 항상 이므로 hhmmss한 자리 시간의 경우 앞에 0을 입력하십시오. 그러면 다음 공식으로 계산할 수 있습니다.

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

이렇게 하면 가장 왼쪽 2자를 '시간'으로, 중간 2자를 '분'으로, 가장 오른쪽 2자를 '초'로 가져와 Excel에서 시간으로 인식하는 것으로 변환합니다. 그런 다음 하나를 다른 것에서 빼고 다음 형식으로 결과를 표시합니다 hhmmss.

편집: 요구 사항이 질문에 지정된 것과 다르기 때문에 패딩을 사용하여 선행 0을 고려하도록 수식을 수정했습니다.

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

가독성이 매우 떨어지지만 이제 값을 0으로 채우고 가장 오른쪽의 6을 사용하므로 사용하는 0의 수에 관계없이 작동합니다.

hh:mm:ss실제로 이 경우 와 같이 결과의 형식을 특별하게 지정하고 싶을 것입니다 .

Answer 2

다음은 입력하려는 방식으로 시간 디코딩을 처리하는 또 다른 방법입니다.

작업을 더 눈에 띄게 만들기 위해 시작 시간과 종료 시간을 형식화되지 않은 숫자로 남겨 두었습니다. 이 예에서 시작 시간은 자정 또는 정오로부터 25초이며, 이를 000025로 입력합니다. 형식을 지정하면 00:00:25처럼 보이더라도 숫자로는 25로 저장됩니다.

이 접근 방식은 100의 거듭제곱인 숫자에서의 위치를 ​​기준으로 시간과 분을 제거합니다. 초는 관계없이 항상 올바른 두 자리입니다. C2의 공식은 다음과 같습니다.

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

MOD 함수는 INT 함수와 반대입니다. 나눈 후 나머지를 제공합니다.

Answer 3

나는 이것이 오래된 게시물이라는 것을 알고 있지만, (나처럼) 사람들이 수많은 포럼을 통해 작업표를 입력하는 시간을 절약하고 수동으로 "콜론"을 입력할 필요 없이 콜론을 표시한 다음 작업을 수행하는 솔루션을 찾고 있다면 계산을 수행할 수 있습니다..... VBA 스크립트, 숨겨진 셀 및 수식을 사용하지 않고도 모두 가능합니다....등...등.... 이것이 제가 생각해낸 솔루션입니다. 0, 24시간제 및 자정에 걸친 작업표(작업표가 여러 자정에 걸쳐 있을 것으로 예상하는 경우 조정이 필요하지만 제 경우에는 이것이 필요하다고 보지 않았습니다).

또한 "초"는 필요하지 않지만 다음 개념을 따르면 충분히 쉽게 확장할 수 있습니다.

시작 시간 및 종료 시간 셀의 형식을 다음과 같이 사용자 정의합니다.

0#":"##

이렇게 하면 "텍스트 셀"로 바뀌지만 숫자를 채워 항상 4자리를 표시합니다(앞에 0이 있거나 미국 친구의 경우 0이 있어도).

참고로 이렇게 하면 4자리 숫자를 입력하면 시간 형식처럼 보입니다. 그래서 이론적으로는 0768년에 진입이 가능하고 당연히 존재하지 않는 07시 68분 시간이 됩니다. 그래서 어느 쪽이든

a) 다음과 같이 데이터 유효성 검사 목록을 만듭니다. - 별도의 워크시트(탭)의 단일 열에 허용 가능한 모든 값의 목록을 만듭니다. 제 경우에는 15분 단위로만 처리했기 때문에 0500, 0515, 0530, 0545.....0445부터였습니다. 초 단위로 드릴다운해야 한다면(진지하게?) 상당히 길어질 것입니다. 050001, 050002.... 등. 또는 이 목록을 만들기 위해 다른 수식을 작성한 다음 텍스트 데이터(값만 붙여넣기)를 새 열에 복사하여 수식을 제거할 수도 있습니다. - "허용되는 값" 셀을 모두 선택하고 도구 모음의 "포럼" 탭에서 "이름 정의"를 선택합니다. "MonkeyTimeList"와 같은 이름을 지정합니다. - 시간표 워크시트로 돌아가서 두 시작 부분을 포함하는 모든 셀을 선택합니다. 및 종료 시간을 선택하고 도구 모음의 "데이터" 탭으로 이동하여 "데이터 유효성 검사> 데이터 유효성 검사> 설정"을 선택하고 "허용" 섹션의 드롭다운에서 "목록"을 선택한 다음 "소스" 섹션에서 유형을 입력하세요. 정의한 이름 앞에 "="가 있으므로 제 경우에는 "=MonkeyTimeList"입니다. "확인"을 클릭하고 "오류 경고" 탭을 선택한 다음 "오류 경고 표시..." 상자를 선택하고 "스타일" 드롭다운에서 "중지"를 선택합니다. "몽키맨이 당신이 바보라고 하더군요. 올바른 형식으로 시간을 입력해 보세요!"와 같은 오류 메시지를 추가한 다음 "확인"을 클릭하세요. "허용" 목록에서 시간으로 입력할 수 있습니다.

b) 이론적으로는 잘못된 데이터가 추가된 셀을 강조 표시하기 위해 조건부 서식을 사용할 수 있습니다. 이렇게 하면 데이터 검증 데이터에 별도의 워크시트를 사용하지 않아도 됩니다. 나는 이런 식으로 해본 적이 없기 때문에 이론적으로 말한다. 다른 튜토리얼일 수도 있겠네요....

따라서 이제 매번 콜론과 Shift 키를 찾을 필요 없이 시간을 입력할 수 있습니다.

셀 형식에서 초를 정말로 원한다면 사용자 정의 셀 형식은 다음과 같습니다.

0#":"##":"##

그래서...... 이제 시작 시간부터 종료 시간까지의 시간을 계산하려면... 다시 말하지만 이것은 분만을 기준으로 합니다.... 그러나 논리를 따를 수 있다면 초까지 확장할 수 있습니다.

세 번째 열(A열은 시작 시간, B는 종료 시간, C는 총 시간이라고 가정)에 다음 수식을 입력합니다.

=IF(VALUE(B2)>=VALUE(A2),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),VALUE(MID(TEXT(B2,"0000"),3 ,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/ 60)),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),24,VALUE(MID(TEXT(B2,"0000"),3,2))/60)) -(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)))

이제 설명하자면...

"IF" 기능은 24시간제 종료 시간을 자정 이전과 자정 이후로 구분합니다. 자정 이후에는 완료 시간에 시작 시간을 뺀 계산이 계속 작동할 수 있도록 완료 시간에 24시간이 추가됩니다. 자정 이전이면 24시간을 추가할 필요가 없습니다. 따라서 계산을 위해 시간 0100은 실제로 2500으로 처리되고, 0200은 2600으로 처리됩니다. 메모. 이는 교대 근무 시간이 23시간 45분을 넘지 않는다고 가정합니다. 더 오래 실행된다면 날짜 열을 수식에 포함해야 할 수도 있습니다.... 다시 말하지만, 필요하지 않았습니다.

MID 함수는 TEXT(따라서 TEXT 함수)에서 작동하며 참조된 셀의 특정 위치에 있는 숫자를 지정된 형식으로 반환합니다.... 정말, "MID(TEXT(B2,"0000"),1,2) "는 B2 셀을 보고 항상 4자리/문자 형식으로 보고 "1" 위치에서 시작하는 두 자리를 반환합니다. 내 경우에는 전체 시간을 나타냅니다. 또 다른 예: "MID(TEXT(A2,"000000"),5,2)"는 셀 A2를 확인하고 위치 "5"로 시작하는 두 자리 숫자(초 단위일 수 있음)를 반환합니다.

반환된 텍스트를 다시 숫자로 변환하여 계산에 사용할 수 있도록 이 모든 것 앞에 "VALUE" 함수를 배치했습니다.

따라서 이를 통해 각 셀에서 시, 분, 초를 별도로 처리할 수 있습니다. 시간 계산은 이미 정수이므로 간단합니다. 그러나 분은 실제로 시간의 분수 또는 60분의 분수이므로 "분"을 나타내는 숫자를 추출한 후 "60"으로 나누어 시간의 "분수"를 제공합니다. 초와 마찬가지로 3600(한 시간의 초 수)으로 나누어 초를 "시간의 일부"로 표시합니다.

이제 시간이 분수와 함께 숫자로 표시되므로 원하는 대로 더하거나 뺄 수 있습니다. 내 공식의 경우 시작 시간과 종료 시간 사이의 총 시간을 제공합니다. Custom> 00.00이라는 수식을 사용하여 셀의 서식을 지정한 다음 시간당 요율 등을 곱할 수 있습니다.

누군가 여전히 공식에서 몇 초를 원하는 경우. 막히면 알려주세요. 직장에서 지루하다면 제가 도와드릴 수도 있습니다.... 하지만 몇 초??? 정말???