아래와 같이 날짜가 텍스트 형식으로 되어 있는 경우 날짜를 변환하는 방법을 알아보겠습니다.
1. 워크시트
가. 함수 이용
DateValue, Date함수를 사용해서 텍스트 형식의 날짜를 날짜 형식으로 변환할 수 있습니다.
DateValue함수는 =DateValue(텍스트 형식의 날짜) 식으로 사용하고,
Date함수는 =Date(연, 월, 일) 식으로 사용하므로, 연도는 왼쪽 텍스트에서 왼쪽 4개 숫자(문자)를 가져오고, 월은 /가 있고 가운데 있으므로 Mid함수를 이용해 A4셀의 텍스트에서 여섯 번째부터 2개를 가져오고, 일은 오른쪽 문자 2개를 가져오면 됩니다. 따라서, 수식이 =date(left(a4,4), mid(A4,6,2), right(a4,2)) 가 되는 것입니다.
나. 선택하여 붙여넣기 > 곱하기 기능 이용
Ctrl + `키를 눌러 다시 수식이 아닌 날짜 형식으로 보이도록 한 다음
D3셀에 1을 입력하고, 복사 메뉴를 누르고,
A3셀부터 A12셀까지 선택한 다음 선택하여 붙여넣기 부분을 누릅니다. 왜냐하면 선택하여 붙여넣기 오른쪽의 꺾기 표시를 누르면
다시 선택하여 붙여넣기를 눌러야 하므로 번거롭기 때문입니다.
그러면 아래와 같은 화면이 나오는데 곱하기를 더블 클릭합니다. 곱하기를 클릭한 후 확인 버튼을 누르는 것보다 편리합니다.
그러면 A열의 텍스트 형식으로 된 날짜가 숫자로 바뀌고 테두리가 없어집니다.
이제 홈 > 표시 형식 그룹에서 일반을 누른 후 간단한 날짜를 선택하면
날짜 형식으로 바뀝니다. 그러나, B2셀과 B3셀의 결과는 문자형식이 아니고, 8자리의 숫자가 아니라 5자리의 숫자이기 때문에 모두 #VALUE! 에러가 발생합니다.
2. VBA로 작성
그러면 테두리를 지우지도 않고 텍스트 형식의 날짜를 날짜형식으로 변경해 보겠습니다.
가. 논리 구성
- A3셀 값은 변수에 저장한 다음 1을 입력하고, 복사해서 A4셀부터 A12셀까지 선택하여 붙여넣기 > 곱하기를 하면 되는데,
- 곱한 다음 숫자로 표시되면 안 되므로 날짜 포맷(YYYY-MM-DD)으로 지정합니다.
- 그리고, A3셀의 값을 변수에 저장한 값으로 복원하면 됩니다.
그런데 유연한 처리를 위해서는
- 생년월일이 1행에 있거나, 2행 이후에 있거나 처리가 가능해야 하고,
- 생년월일이 1개만 입력되거나, 2개 또는 3개 이상 입력된 경우에 대한 대비를 해야 합니다.
나. 코드 작성
작성된 코드는 아래와 같습니다.
Sub 날짜변환()
Dim col_num As Range
Dim prev_value As Date
Dim prev_range As Range, paste_cells As Range
Set col_num = Application.InputBox("날짜를 변환할 열을 선택하세요.", Type:=8)
If col_num(1, 1).End(xlDown).Row = Cells(Rows.Count, col_num.Column).End(xlUp).Row Then
Set prev_range = col_num(1, 1).Offset(1, 0)
Else
Set prev_range = Cells(1, col_num.Column).End(xlDown).Offset(1, 0)
End If
If prev_range.CurrentRegion.Rows.Count = 2 Then
prev_range = DateValue(prev_range)
prev_range.NumberFormat = "yyyy-mm-dd"
End
Else
prev_value = DateValue(prev_range)
prev_range = 1
prev_range.Copy
If prev_range.CurrentRegion.Rows.Count = 3 Then
Set paste_cells = prev_range.Offset(1, 0)
Else
Set paste_cells = Range(prev_range.Offset(1, 0), prev_range.Offset(1, 0).End(xlDown))
End If
End If
paste_cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Range(prev_range, prev_range.End(xlDown)).NumberFormat = "yyyy-mm-dd"
prev_range = prev_value
prev_range.Select
Application.CutCopyMode = False
End Sub
다. 코드 설명
① 변수 선언
| Dim col_num As Range Dim prev_value As Date Dim prev_range As Range, paste_cells As Range |
Dim col_num As Range : 생년월일이 입력된 열을 저장할 변수 col_num을 Range(범위) 형식으로 지정합니다.
Dim prev_value As Date : A3셀의 이전 값을 저장할 변수를 prev_value라고 하고, 날짜 형식으로 지정합니다.
Dim prev_range As Range, paste_cellsAs Range : A3 셀 등 종전 위치를 prev_range변수에 범위형식으로 선언하고, 붙여넣을 범위를 paste_cells란 변수로 선언합니다.
② 생년월일 등 날짜 변환열 지정
| Set col_num = Application.InputBox("날짜를 변환할 열을 선택하세요.", Type:=8) |
Application.InputBox 메소드를 이용해 "날짜를 변환할 열"을 범위 형식으로 입력받아(Type:=8) col_num 변수에 저장합니다.
③ 복사할 셀(prev_range) 지정 및 복사할 셀에 있는 값 저장
| If col_num(1, 1).End(xlDown).Row = Cells(Rows.Count, col_num.Column).End(xlUp).Row Then Set prev_range = col_num(1, 1).Offset(1, 0) prev_value = DateSerial(Left(prev_range, 4), Mid(prev_range, 6, 2), Right(prev_range, 2)) Else Set prev_range = Cells(1, col_num.Column).End(xlDown).Offset(1, 0) prev_value = DateSerial(Left(prev_range, 4), Mid(prev_range, 6, 2), Right(prev_range, 2)) End If |
If col_num(1, 1).End(xlDown).Row = Cells(Rows.Count, col_num.Column).End(xlUp).Row Then :
날짜를 변환할 열에서 1행 1열에서 Ctrl + ↓ 키를 눌렀을 때처럼 연속된 맨 아래셀의 행 수와 워크시트의 맨 아랫줄(최신 엑셀이라면 1,048,576행)에서 Ctrl + ↑키를 눌렀을 때처럼 위로 올라와서 만나는 맨 아랫줄의 행수가 같을 때는 생년월일 헤더가 1행에 있을 때입니다.
- If 조건을 충족했을 때의 처리:
Set prev_range = col_num(1, 1).Offset(1, 0) : 선택한 열의 1행 1열에서 한 줄 아래 셀 주소를 prev_range에 저장합니다. 다시 말해 생년월일 아래 셀 주소가 prev_range가 됩니다.
Else : 생년월일 헤더가 2행 아래에 있는 경우입니다.
- Else 조건에 해당한 경우의 처리
Set prev_range = Cells(1, col_num.Column).End(xlDown).Offset(1, 0) : 생년월일이 1행에 있는 것이 아니므로 1행 열 번호에 해당하는 셀에서 맨 아래로 내려가서 생년월일이 있는 셀로 이동한 다음 그 아래 셀 주소를 prev_range에 저장합니다.
Cells(1, col_num.Column)는 col_num(1, 1)과 같습니다.
prev_value = DateSerial(Left(prev_range, 4), Mid(prev_range, 6, 2), Right(prev_range, 2)) : prev_vale는 if문과 관계없이 동일하므로 End If 다음에 썼습니다.
prev_range에서 왼쪽 4글자, 6번째부터 2개, 오른쪽 2개를 가져와 연, 월, 일로 입력하면 DateSerial함수는 날짜 형식으로 변환해 주며, 그 값을 prev_value 변수에 저장하는 것입니다.
Microsoft 365 등 최신 버전이라면 prev_value = DateValue(prev_range)로 바꿀 수 있습니다.
④ 생년월일이 입력된 줄이 1개, 2개 또는 3개 이상인 경우에 따라 달리 처리
| If prev_range.CurrentRegion.Rows.Count = 2 Then prev_range = DateValue(prev_range) prev_range.NumberFormat = "yyyy-mm-dd" End Else prev_value = DateValue(prev_range) prev_range = 1 prev_range.Copy If prev_range.CurrentRegion.Rows.Count = 3 Then Set paste_cells = prev_range.Offset(1, 0) Else Set paste_cells = Range(prev_range.Offset(1, 0), prev_range.Offset(1, 0).End(xlDown)) End If End If |
if문이 if ~ Else ~ End If로 되어 있고, Else안에 다시 If ~ Else ~ End If가 들어 있습니다.
If prev_range.CurrentRegion.Rows.Count = 2 Then : prev_range를 기준으로 한 현재 영역의 행 수가 2인 경우 다시 말해 생년월일 헤더와 생년월일 입력이 한 줄인 경우입니다.
- If 조건문을 충족할 경우의 처리
prev_range = DateValue(prev_range) : 생년월일 아래 셀에 텍스트로 입력된 날짜를 날짜 형식으로 변환해서 다시 생년월일 아래 셀에 넣습니다.
prev_range.NumberFormat = "yyyy-mm-dd" : 복사할 셀의 숫자 형식을 yyyy-mm-dd, 다시 말해 연도 네 자리, 월과 일은 2자리, 그리고 중간에 하이픈을 넣은 형식으로 지정합니다.
End: Sub Procedure를 끝내는 것입니다. 왜냐하면 생년월일이 입력된 줄이 한 줄인 경우는 다른 줄에 복사할 필요없이 날짜로 변환 후 그 셀에 입력하기만 하면 되기 때문입니다.
Else : 생년월일 입력 줄이 2줄 이상인 경우
- Else인 경우의 처리
(공통 사항)
prev_value = DateValue(prev_range) : 1로 바꾼 다음 복사할 것이기 때문에 기존에 있던 값을 prev_value 변수에 저장합니다.
prev_range = 1 : 생년월일이 입력된 첫번째 셀에 1을 입력합니다.
prev_range.Copy : 생년월일이 입력된 첫번째 셀, 다시 말해 1을 복사합니다.
(다시 조건문)
If prev_range.CurrentRegion.Rows.Count = 3 Then : prev_range 기준 현재 영역의 행 수가 3개인 경우는 생년월일이 입력된 행이 2개인 경우입니다.
- If문을 충족했을 경우의 처리
Set paste_cells= prev_range.Offset(1, 0) : 붙여넣을 범위를 prev_range의 한 줄 아래셀로 해서 cell_str에 저장합니다.
Else : 첫번째 If문과 Else문 안의 If문의 조건을 모두 충족하지 못할 경우, 다시 말해 생년월일을 입력한 줄이 3줄 이상인 경우입니다.
Set paste_cells = Range(prev_range.Offset(1, 0), prev_range.Offset(1, 0).End(xlDown)) : prev_range의 한 줄 아래부터 한 줄아래에서 연속된 맨 아래 셀까지를 paste_cells 변수에 저장합니다.
⑤ 선택하여 붙여넣기 - 곱하기
| paste_cells.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False |
paste_cells.Select : 붙여넣을 셀을 선택합니다.
Selection.PasteSpecial : 선택된 영역에 선택하여 붙여넣기 합니다.
Paste:=xlPasteAll : 붙여넣기의 종류로 xlPasteAll은 일반 붙여넣기이며, 값으로 붙여넣기인 xlPasteValues가 있습니다.
Operation:=xlMultiply : 연산(Operation) 중 곱하기를 의미합니다. Add는 더하기, Subtract는 빼기, Divide는 나누기입니다.
SkipBlanks:=False : 복사할 값이 공백인 경우를 건너뛸 것인지 정하는 것으로 False이므로 건너뛰지 않습니다.
Transpose:=False : 행/열 바꿈 여부로 False이므로 행과 열 방향을 바꾸지 않습니다.
⑥ 표시 형식을 날짜 형식으로 지정하기
| Range(prev_range, prev_range.End(xlDown)).NumberFormat = "yyyy-mm-dd" |
위에서도 날짜 형식을 지정했는데, GoTo 명령어를 사용해서 이 부분을 건너뜁니다. prev_range에서 연속된 행의 끝까지를 날짜 형식으로 지정하는 것입니다.
⑦ 종전 값 복원 및 복사 모드 끄기
| prev_range = prev_value prev_range.Select Application.CutCopyMode = False |
prev_range = prev_value : prev_value에 저장한 값을 prev_range에 입력합니다. 다시 말해 기존 날짜를 복원하는 것입니다.
prev_range.Select : prev_range 다시 말해 생년월일 아래 셀을 선택(클릭)합니다.
Application.CutCopyMode = False : 복사한 영역을 해제합니다.
라. 실행
실행하기 전에 날짜를 먼저 텍스트 형식으로 바꾸기 위해 아래와 같이 2 앞에 작은따옴표를 입력한 다음 엔터 키를 누릅니다. 그리고, 아래로 복사하기 위해 채우기 핸들을 더블 클릭합니다.
그러면 날짜가 모두 텍스트 형식으로 바뀝니다.
① 중단점 설정
중단점을 설정할 줄을 클릭한 다음 F9키를 누르거나, 왼쪽 기둥 부분을 마우스로 클릭해서 아래와 같이 두 군데 If 조건문에 중단점을 설정합니다.
② 실행
표준 도구모음에서 엑셀 아이콘을 눌러 워크시트로 돌아온 후 개발도구 - 매크로에서 날짜변환을 더블 클릭합니다.
그러면 아래와 같이 "날짜를 변환할 열을 선택하라"라고 하므로
A열을 클릭합니다.
그러면 입력 대화상자에 $A:$A가 입력됩니다. 확인 버튼을 누르면,
첫 번째 중단점에 멈춥니다. 어디로 분기하는지 확인하기 위해 F8키(한 단계씩 코드 실행)를 누릅니다.
Else문으로 분기합니다. 생년월일이 1행에 있지 않기 때문입니다.
이번에는 F5키 또는 실행 아이콘(▶)을 누르면 두 번째 중단점에 멈춥니다. F8키를 3번 누르면
Else문 안의 Set 문으로 이동합니다. 이것은 현재 영역의 행 수가 4 이상이라는 의미입니다. 다시 F8키를 계속 누르면
이제 F5키를 다시 누르면 실행을 종료합니다.
여전히 VB Editor에 머물러 있으므로 Excel 아이콘을 눌러 워크시트로 돌아가서 확인하면,
날짜가 날짜 형식으로 잘 표시되고, 테두리도 유지되고 있습니다.
다시 VB Editor로 돌아가서 디버그 > 모든 중단점 지우기 메뉴(단축키 : Ctrl + Shift + F9)를 눌러 모든 중단점을 지웁니다.
생년월일 입력 줄을 2줄 또는 1줄로 만들고도 해보고, 생년월일 헤더를 1행으로 이동하거나 생년월일 헤더를 D열로 이동한 후 실행해서 에러가 발생하는지 확인해 보기 바랍니다.
마. personal.xlsb에 저장
위와 같이 엑셀 매크로 파일에 저장하는 경우는 그 파일에만 적용되는데, personal.xlsb에 저장하면 어떤 파일을 열든 모두 적용할 수 있는 장점이 있으므로 personal.xlsb에 저장하기 바랍니다.
personal.xlsb에 저장하려면 먼저 personal.xlsb 파일을 만든 다음( https://lsw3210.tistory.com/entry/%EA%B0%9C%EC%9D%B8%EC%9A%A9-%EB%A7%A4%ED%81%AC%EB%A1%9C-%ED%86%B5%ED%95%A9%EB%AC%B8%EC%84%9C-personalxlsb 참고)
날짜변환예제(완성)에 있는 날짜변환 코드를 복사해서
personal.xlsb 파일의 Module1을 더블 클릭한 후 붙여넣으면 됩니다..
3. 나이 구하기
나이는 DateDif 함수를 이용해서 =DATEDIF(A3,TODAY(),"y")라고 입력해서 구하면 됩니다.
DateDif 함수의 인수는 시작일, 종료일, 계산 단위입니다. "y"란 연 단위로 시작일과 종료일 사이의 해(연)를 구하라는 의미입니다.
'EXCEL - VBA' 카테고리의 다른 글
| Range.AutoFilter Method(1) - expression.AutoFilter (0) | 2023.03.02 |
|---|---|
| 날짜 변환 - DateValue 함수, 선택하여 붙여넣기 VBA(2) (0) | 2023.02.17 |
| 엑셀 VBA - XML Parsing(XML 6.0 기준) (4) (LoadXML - 2) (0) | 2023.02.14 |
| 엑셀 VBA - XML Parsing(XML 6.0 기준) (3) (LoadXML - 1) (0) | 2023.02.13 |
| 엑셀 VBA - XML Parsing(XML 6.0 기준) (1) (0) | 2023.02.08 |