구글 스프레드시트의 날짜 형식 변경하는 법(챗GPT로 수식 만들기)

한빛미디어

2024-06-20

by 강남석

7,797

✅스프레드시트 날짜 형식 변환(‘월/일/연도’을 ‘연도/월/일’로)

구글 스프레드시트의 REGEXREPLACE, REGEXMATCH, REGEXEXTRACT는 정규식으로 추출한 문자열의 패턴을 이용할 수 있게 해주는 강력한 함수입니다. 그러나 정규식은 복잡하고 평소에 쓸 일이 많지 않아서 공부하기에는 부담이 됩니다. 복잡한 정규식을 챗GPT에게 맡겨볼까요?

회사의 대시보드 시스템에서 데이터를 다운로드하고 월별 합계를 내려고 합니다. 월별 합계를 내려면 날짜 열을 날짜 서식으로 변환해야 하는데 변환이 어려운 형태로 되어 있습니다. 가장 좋은 방법은 개발자에게 원시 데이터를 날짜 형태로 바꿔달라고 요청하는 것이지만, 챗GPT의 도움을 받아 직접 해보겠습니다.무료로 사용할 수 있는 챗GPT 3.5 버전을 사용합니다(2024년 6월 기준).

01 예제 | GPT_REGEX(145행)의 A열에는 날짜가 M월 D, YYYY HH:MM오전 형태로 입력되어 있습니다. 연, 월, 일을 추출하여 DATE 함수에 넣어도 되지만 DATE 함수는 배열을 인수로 받지 못하기 때문에 연, 월, 일을 별도의 열에 추출해야 해서 시트가 지저분해집니다.

02 시간을 나타내는 문자열은 필요 없으니 삭제하고, 날짜를 나타내는 문자열은 YYYY/MM/DD 형태로 만들어서 DATEVALUE 함수에 적용해 보겠습니다.
이때 고려해야 할 것은 월/일의 자릿수입니다. 2024년 1월 1일과 2023년 12월 31일을 비교해보세요.

03 REGEXREPLACE 함수를 두 번 사용하면 문자열의 패턴을 지정하여 삭제하거나 순서를 바꿀 수 있습니다. 여기서는 챗GPT에게 도움을 요청해보겠습니다.
웹 브라우저를 열고 챗GPT(https://chat.openai.com)로 이동하여 입력란에 다음과 같이 프롬프트를 입력하고 메시지를 보냅니다.

1월 1, 2024 12:00오전
12월 31, 2023 12:00오전

위와 같은 문자열을 각각 2024/01/01, 2023/12/31로 바꾸는 구글 시트 수식을 만들려고 해.

1. REGEXREPLACE로 시간 부분을 삭제해 줘.
2. REGEXREPLACE로 앞쪽 문자열의 순서를 현재의 월/일/연도에서 연도/월/일로 바꿔 줘.
3. 변환된 문자열은 DATEVALUE 함수에 넣어서 날짜 형태로 바꿔 줘.

*인공지능으로부터 좋은 코드를 얻기 위해서는 사용자의 요구 사항을 인공지능에게 명확하게 전달해야 합니다. 이때 요구 사항, 즉 프롬프트(prompt)에 인공지능의 역할, 사용자, 배경지식, 작업, 규칙, 답변의 형식 등을 포함하면 도움이 됩니다. 자주 하는 실수는 미리 주의를 주는 것도 좋습니다.

아무리 좋은 프롬프트를 만들어도 인공지능이 틀린 답을 줄 수 있습니다. 당황하지 마세요! 시행착오를 거치더라도 직접 코드를 작성하는 것보다는 인공지능에게 맡기는 쪽이 훨씬 편리합니다.

04 챗GPT가 지시 사항을 잘 이해하고 멋진 수식을 제공합니다. [Copy code] 버튼을 눌러 수식을 복사합니다.

= DATEVALUE(
REGEXREPLACE(
REGEXREPLACE(A1, "₩d{2}:₩d{2}[오전|오후]*", ""),
"(₩d{4})년 (₩d{2})월 (₩d{2})일", “$1/$2/$3”
)
)

*챗GPT의 응답은 랜덤으로 생성됩니다. 그러니 위의 예시와 다른 수식을 알려주더라도 당황하지 마세요. 일단 진행하며 조금씩 오류를 수정해 나가면 됩니다.

05 복사한 수식을 [data] 시트의 [F2] 셀에 입력합니다. 날짜 문자열이 기재된 곳이 [A1] 셀이 아니라 [A2] 셀이므로 수식을 수정해야 합니다. 수정한 수식은 =DATEVALUE(REGEXREPLACE(REGEXREPLACE(A2, "d{2}:d{2}[오전|오후]*", ""), "(d{4})년 (d{2})월 (d{2})일", "$1/$2/$3"))입니다.

06 문자열이 날짜 서식으로 변환되었습니다. 아래 셀들까지 모두 입력될 수 있도록 수식에 ARRAYFORMULA를 적용하고 범위를 [A2:A]로 연장합니다.
완성된 수식은 =ARRAYFORMULA (IF(A2:A<>"", DATEVALUE(REGEXREPLACE(REGEXREPLACE(A2:A, "d{2}:d{2}[오전|오후]*", ""), "(d{4})년 (d{2})월 (d{2})일", "$1/$2/$3" ) ), ""))입니다.