엑셀 날짜와 시간으로 입력된 데이터를 날짜와 시간으로 분리 시키는 수식 작성 방법.

안녕하세요. 행아아빠입니다.

엑셀로 근로시간, 근태관리를 하는경우 다양한 형태의 서식으로 사용자의 환경과 취향에 맞도록 관리하는 경우가 있습니다. 그중에서도 종종 문의 주시는 형태로 "2023-03-02 07:00"과 같이 날짜와 시간으로 입력되는 데이터를 날짜와 시간데이터로 각각 분리 하고자 할 경우 활용할 수 있는 수식 작성 방법이 되겠습니다.

 

일반적인 근로시간, 근태관리, 급여관리에 필요한 자동 계산 서식과 수식 작성 방법은 다음의 링크를 통해서 샘플 서식 다운로드와 수식 작성 방법을 확인하실수도 있습니다.

2023년 엑셀 급여 계산 서식과 수식 작성.

2023년 엑셀 급여 계산 서식과 수식 작성.

 

---

■ 행아아빠의 Youtube 채널.

● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕

● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom

■ 행아아빠 후원 안내.

● Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join

● Youtube 댓글 후원 - Youtube 영상 댓글에서 "Super Thanks" 구매 후 댓글 작성.

● 내일은엑셀왕 대표 Youtube 영상 - https://youtu.be/oYVZHgv_sKM (2023년 급여 자동 계산 서식)

 

 

---

1. "날짜 시간" 데이터 형식을 날짜, 시간으로 분리 시키는 수식 작성.

날짜와 시간으로 입력된 데이터를 날짜와 시간으로 분리 시키는 수식 작성 방법 예제 이미지.

예제 이미지에서 보는것과 같이 A열에는 날짜시간, B열에는 출근 또는 퇴근을 알 수 있는 상태, C열에는 이름이 입력된 데이터를 참조해서 E열에는 날짜, F열에는 출근시간, G열에는 퇴근시간으로 각각 분리시키는 수식을 작성해 보도록 하겠습니다.

 

1-1. 날짜를 분리 시키는 함수와 수식.

A열에 입력되어 있는 "2023-01-26 07:50:57"과 같은 형식의 데이터에서 날짜만을 분리시켜서 결과값으로 반환 시키기 위한 함수는 Date함수를 활용해서 다음과 같이 수식을 작성할 수 있습니다.

E2셀 수식. =DATE(YEAR($A2),MONTH($A2),DAY($A2))

 

 

1-2. 출근시간과 퇴근시간으로 각각 분리 시키는 수식.

A열에 입력되어 있는 "2023-1-26 07:50:57"과 같은 형식의 데이터에서 출근시간과 퇴근시간으로 각각의 시간을 따로따로 분리시켜서 표기 하기 위해서는 B열의 상태를 참조해서 구분해야 합니다.

 

B열에 입력되어 있는 상태 데이터는 출근(얼굴), 출입(얼굴), 출입(카드), 퇴근(얼굴)과 같이 4종류의 데이터로 분류되서 입력되어 있는데 출근(얼굴), 출입(얼굴), 출입(카드)의 데이터는 출근시간으로 하고, 퇴근(얼굴)은 퇴근시간이라고 할 경우 A열에 입력된 데이터값을 출근시간과 퇴근시간으로 각각 분리 시켜서 결과값으로 반환하기 위한 수식을 다음과 같이 작성할 수 있습니다.

 

출근과 퇴근을 구분하기 위한 참조 범위 이미지.

수식을 작성하기 이전에 B열에 입력된 데이터의 분류를 참조할 수 있도록 참조시트를 추가해 주도록 하고, B열에 입력된 데이터가 출근(얼굴), 출입(얼굴), 출입(카드)일 경우에는 A열에 입력된 데이터에서 시간으로 분류시킬 결과값을 출근시간의 결과값으로 반환합니다.

 

그리고 퇴근(얼굴)의 데이터가 입력될 경우에는 A열에 입력된 데이터에서 시간으로 분류시킬 결과값을 퇴근시간의 결과값으로 반환하기 위한 참조할 데이터 표를 간단하게 작성해 주도록 하고, 만약 B열에 출근과 퇴근을 분류시키기 위한 데이터 분류값이 더 많다면 추가로 출근과 퇴근을 분류시키기 위한 데이터 분류값의 종류를 참조시트에 추가해 주도록 합니다.

1. F2셀 - 출근시간 수식. =IF(COUNTIFS(참조!$A$1:$A$10,Sheet1!$B2)>=1,TIME(HOUR($A2),MINUTE($A2),SECOND($A2)),"") 2. G2셀 - 퇴근시간 수식. =IF(COUNTIFS(참조!$B$1:$B$10,Sheet1!$B2)>=1,TIME(HOUR($A2),MINUTE($A2),SECOND($A2)),"")

여기까지 수식을 작성하게 되면 날짜, 출근시간, 퇴근시간을 각각 분류시킬 수 있기 때문에 이제 날짜와 이름, 출근시간, 퇴근시간을 기준으로 근로시간을 계산하는 수식을 작성해서 근로시간, 근태, 급여등의 원하는 데이터를 계산하기 위한 수식과 서식을 사용자의 환경에 맞도록 작성할 수 있습니다.

 

Blog : https://hang-a-fafa.tistory.com

Youtube : https://www.youtube.com/@economic-freedom

Naver 까페 : https://cafe.naver.com/hatime