엑셀 날짜 관련 함수 총정리(3) (year, month, day, hour, minute, second, date, time 함수)

연,월,일을 구하는 year, month, day 함수와 시,분,초를 구하는 hour, minute, second 함수 및 날짜와 시간 표시와 관련된 date, time 함수에 대해 알아보겠습니다.

날짜관련함수3.xlsx

0.02MB

 

3. 날짜 및 시간 분리하기

​

① year, month, day 함수

year함수는 날짜에서 연도를 구해주는 함수로서, 입력형식은 =year(날짜 또는 날짜 시간) 이며,

month함수는 날짜에서 월을 구해주는 함수로서, 입력형식은 year가 month로 바뀔 뿐 인수는 같습니다.

day함수는 날짜에서 일을 구해주는 함수로서, 입력형식은 함수명이 day라는 것만 다르고, 인수는 같습니다.

​

㈀ 위 파일을 연 후 C열부터 K열까지 마우스를 끌어서 선택하고, 마우스 오른쪽 버튼을 눌러 컨텍스트 메뉴가 나오게 한 후 '내용 지우기' 메뉴를 누르거나,

 

홈 - 지우기 - 내용 지우기 메뉴를 누릅니다.

 

㈁ 그럼 기존 내용이 아래와 같이 모두 지워지고, 다만, 테두리 및 서식 등은 남아 있습니다. 만약 홈 - 지우기 - 모두 지우기를 선택하면 테두리 뿐만 아니라 서식, 메모 등 모든 것이 지워집니다.

 

㈂ c5셀부터 k5셀까지 year, month, day, time1, hour, minute, second, date, time2 라고 입력합니다.

 

㈃ c6셀에

=year($b6)

라고 입력하고, 엔터키를 칩니다.

$b6은 b6이라고 입력한 후 F4키를 3번 누르거나 shift+4키를 이용해서 $를 입력해도 됩니다.

그리고, 맨 오른쪽 괄호는 입력하지 않고 엔터키를 쳐도 제대로 괄호가 닫혀서 입력됩니다. 괄호 하나 안치는 것도 노력 절감입니다.

 

㈄ 결과값이 날짜 형식으로 되어 있으므로 Ctrl + 1을 눌러 '표시형식' 탭에서 '일반'을 더블 클릭하거나,

 

홈 메뉴의 표시형식 콤보박스를 클릭하여 '일반'으로 바꿉니다.

 

㈅ 그러면 아래와 같이 숫자 타입으로 바뀝니다.

 

㈆ C6셀 오른쪽 아래 네모로 된 채우기 핸들을 마우스 왼쪽 버튼으로 끌어서 E6셀까지 수식을 복사합니다.

그리고, 나서 D6셀의 year를 month로 바꾸는데, mon 까지 입력하면

아래와 같이 month와 emonth가 제시되고, month가 선택되어 있으므로 Tab키를 쳐서 month를 완성하고 엔터키를 칩니다. 옆에 month 함수에 대한 설명도 표시되고 있습니다.

 

E6셀의 year를 day로 바꿀 때는 먼저 year 부분을 fx 오른쪽의 year 부분을 마우스로 더블 클릭해서 선택하고,

 

da까지 입력하면 da로 시작하는 함수들이 나열되는데, 화살표키로 이동하거나 마우스로 클릭하여 day를 선택하고, Tab키를 쳐서 day란 글자를 완성한 다음 엔터키를 칩니다.

 

​

② hour, minute, second 함수

 

㈀ 시간, 분, 초를 구해야 하는데, 시간 데이터가 없으므로 F6셀에

01:01:01

이라고 입력합니다.

그런데, 01:01:01 이 아닌 1:01AM 으로 표시됩니다.

 

㈁ 이것보다는 시:분:초로 보는 것이 편하므로

Ctrl + 1키를 눌러 '표시형식'을 선택한 후 '시간'을 선택하고, 형식은 13:30:55를 더블 클릭합니다.

 

㈂ 그러면 아래와 같이 1:01:01로 표시됩니다. 분과 초는 01식으로 앞 자리가 채워지는데, 시는 1로 표시됩니다.

 

㈃ 시간도 01로 바꾸려면 '시간' 범주에는 없고, 아래 '사용자 지정'에서 h:mm:ss를 클릭한 다음, 형식 아래 입력칸 h앞에 커서를 넣고 h를 추가해서 hh:mm:ss 로 바꾼 다음 엔터키를 치거나, 확인 버튼을 누르면 됩니다.

 

그러면 아래와 같이 01:01:01로 표시형식이 달라집니다. 그러나 입력된 시간은 여전히 1:01:01 AM이라고 되어 있어 다릅니다.

 

㈄ 이제 시,분,초를 구해 봅시다.

'시'를 구하기 위해

g6셀에

=hour($f6)이라고 입력합니다.

​

㈅ '분'을 구하기 위해 g6셀을 끌고 수정하던지, 아니면 직접 h6셀에

=minute($f6)

이라고 입력합니다.

​

㈆ '초'를 구하기 위해 i6셀에

=second($f6)

이라고 입력합니다.

​

㈇ 시,분,초가 각각 1로 구해졌습니다.

 

㈈ 이제 C6셀부터 i6셀까지 마우스로 끈 다음, 맨 오른쪽의 네모로 된 채우기 핸들을 더블 클릭해서

 

i30셀까지 수식을 복사합니다.

한 가지 아쉬운 점이라면 25;25:25초가 25시로 표시되거나 1일 01:25:25로 표시되어야 하는데, 그렇게 안되어 있다는 것입니다.

​

㈉ 해결책 1 - 25시로 표시하는 방법

표시형식 - 사용자 지정에서 [h]:mm:ss를 선택하면

 

아래와 같이 25:01:01로 표시됩니다. 그러나 hour 함수로 구한 시간은 여전히 1입니다. 1일 1시간이니 당연히 1이 맞죠.

 

㈊ 해결책 2 - 1일 1시로 표시하는 방법

ⓐ 표시형식을 사용자 지정에서 d hh:mm:ss로 지정하면 되는데, 형식에 해당하는 것이 없으므로, 아래와 같이 hh:mm:ss 를 선택한 다음 윗 부분 입력란에 d를 추가하여 d hh:mm:ss 로 바꾸고 엔터키를 칩니다.

ⓑ 그러면 아래와 같이 1 01:01:01로 표시되는데, 여전히 hour함수의 결과값은 여전히 1입니다.

​

ⓒ 시간 표시 뒤에 있는 1이 날짜인지 불분명하므로 날짜라는 것을 확실히 할 수 있도록 1일로 표시하려면, 형식에 d일 hh:mm:ss 라고 "일"을 추가하면 윗 부분 보기에 '1일'이라고 바뀐 것을 확인할 수 있고, 확인 버튼을 누르면

 

ⓓ 아래와 같이 1일 01:01:01로 셀 값이 바뀝니다.

ⓔ 엑셀이 참 똑똑한 것이 d일 hh:mm:ss 라고 입력했지만 표시형식을 다시 들어가 보면 d"일" hh:mm:ss 로 일에 큰 따옴표가 자동으로 입력됩니다.

 

ⓕ 그리고, 사용자 지정형식으로 그동안 추가했던 형식이 목록 아래에 추가되어 있음을 알 수 있습니다.

 

ⓖ 시간도 25시간으로 표시할 수 있을까 하고, g30셀의 표시형식을 [h]로 바꿨는데, 25가 아닌 24로 나옵니다. 왜 그런지 모르겠네요?

​

 

4. 날짜 및 시간 합치기

 

분리된 연,월,일과 시,분,초를 합쳐서 표시할 수 있습니다.

​

① 연,월,일을 날짜로 합치기 - date 함수

㈀ 날짜를 표시하는 함수는 date로 인수는 year, month, day 입니다.

연,월,일을 구하는 것이 날짜를 분리한 것이라면, 이를 합치는 것이 date 함수입니다.

​

㈁ J6셀에

=date(c6,d6,f6)

이라고 입력하고 엔터키를 칩니다.

c6,d6,f6은 키보드로 입력하는 것보다는 마우스로 해당 셀을 클릭하는 것이 편합니다.

​

㈂ 또 다른 방법은 입력 줄 왼쪽 fx 아이콘을 클릭해서 함수 마법사를 호출한 후 날짜/시간 범주에서 date를 더블 클릭해서 date 함수를 선택한 다음

 

화면에 인수로 year, month, day가 표시되는데,

year 오른쪽에 커서를 넣고 C6셀을 마우스로 클릭하고,

month 오른쪽에 커서를 넣고 D6셀을 마우스로 클릭하고,

day 오른쪽에 커서를 넣고 E6셀을 마우스로 클릭해서 인수를 모두 입력하면

​

 

오른쪽 아래에 =41275 라고 결과값이 표시됩니다. 41275는 2013-01-01에 해당하는 일련번호입니다.

이제 확인 버튼을 누르면

2013-01-01 이란 날짜가 입력됩니다.

​

② 시,분,초를 시간으로 합치기 - time 함수

㈀ 시간을 표시하는 함수는 time으로 인수는 hour, minute, second 입니다.

시,분,초를 구하는 것이 시간을 분리한 것이라면, 이를 합치는 함수가 time입니다.

​

㈁ k6셀에

=time(g6,h6,i6)

이라고 입력하고 엔터키를 칩니다.

​

㈂ 그러면 아래와 같이 결과값이 나오는데, f6셀에서 있었던 것과 같이 시:분:초가 아닌 시:분 AM으로 표시되고 있습니다.

 

㈃ 표시형식을 [h]:mm:ss로 바꾸겠습니다.원하는대로 표시됩니다.

 

㈄ j6셀에서 k6셀을 선택한 후 채우기 핸들을 더블 클릭해서 K30셀까지 복사하겠습니다.

 

㈅ time 함수로 만든 시간은 표시형식이 [h]:mm:ss인데도 25로 표시되지 않습니다. 한계가 있네요.

​

③ 날짜와 시간 함께 표시하기

㈀ 날짜와 시간을 함게 표시하려면 날짜 + 시간 하면 됩니다. 그런데, 화면에 보이는 것은 기존 서식에

따라 다르므로 표시형식을 yyyy-mm-dd hh:mm:ss 로 바꿔주는 것이 필요합니다.

​

㈁ 먼저 수식을 넣을 열을 만들기 위해, L열의 L이라는 글자 부분에 마우스 커서를 놓고, 마우스 오른쪽 버튼을 눌러 삽입 메뉴가 나오게 한 후 삽입 메뉴를 눌러 L열을 삽입하겠습니다.

​

㈂ L5셀에 '날짜 시간'이라고 적겠습니다.

​

㈃ L6셀에

=j6+k6

라고 입력하는데, j6와 k6는 키보드로 입력하는 것보다는 마우스로 클릭해서 입력하는 것이 편합니다.

​

ⓔ 결과값이 열 너비보다 넓어서 ######### 이라고 표시되기 때문에 '날짜 시간'이 제대로 보이지 않습니다.

​

㈄ L열과 M열 사이 세로선 부분에 마우스 커서를 놓고 더블 클릭해서 열 너비를 자동 조절합니다.

㈅ 날짜가 이상합니다. 날짜 시간 표시형식을 Ctrl + 1 키를 누른 후 사용자 지정에서 확인해보니 [h]:mm:ss로 되어 있어서 그랬네요. 그리고 형식에 원하는 yyyy-mm-dd hh:mm:ss 는 없고 yyyy-mm-dd h:mm만 있습니다.

㈆ 위 형식을 수정해서 yyyy-mm-dd hh:mm:ss 로 바꾸고 확인 버튼을 누릅니다.

 

㈇ 그러면 아래와 같이 제대로 표시됩니다. '날짜 시간'에 맞게 열 너비도 자동으로 조절됐습니다.

 

㈈ L6셀 오른쪽 채우기 핸들을 더블 클릭하여 L30셀까지 복사합니다.

그런데, L30셀 값이 이상하네요. 하루가 지났으니 9-13 01시로 표시돼야 하는데, 9-12 01시로 되어 있습니다. 시간을 날짜로 올리지를 못하네요.

​

㈉ M열을 삽입하고, m5셀에 '날짜 시간2'라고 입력하고,

m6셀에

=b6+f6

라고 입력하여,

date나 time함수가 아닌 숫자로 된 값을 더하는 것으로 바꿉니다.

​

㈊ m6셀의 채우기 핸들을 더블 클릭해서 m30셀까지 복사합니다.

m30셀의 값이 9-13 01시로 L30셀과 달리 정확하게 계산되었습니다. time함수는 한계가 있네요.

 

날짜관련함수3(완성).xlsx

0.02MB