유용한 스마트시트 날짜 수식 활용
그동안 스마트시트에서 나름 고민해서 날짜 관련한 수식을 적용한 적이 있다. 그 당시에는 고민해서 적용했는데 나중에 다시 재사용을 하려고 하면 그 당시 사용한 시트를 찾아서 확인해야 하다 보니 찾기가 번거로워서 간단히 기록을 해놓으려고 한다. 스마트시트의 함수, 수식은 구글 시트와 조금 다르기 때문에 가끔 헷갈리는 경우가 있다. 새로 적용한 수식들은 이 글에 계속 업데이트를 할 예정이다.
텍스트로 입력받아 날짜로 변환하여 적용하기
다른 부서에서 텍스트로 받은 날짜에 연산을 하고 싶다는 문의를 받았다. 양식을 통해서 제품 전시를 위한 입고일과 사용 기간을 각각 dropdown list로 입력받아서 계산하고 싶다고. 이게 구글 시트에서는 잘 되는데 스마트시트에서는 안된다며..
이렇게 dropdown으로 입력을 받아서
이렇게 함수를 적용처음부터 날짜 타입으로 받으면 좋겠지만 매주 월요일에만 입고가 가능하기 때문에 저렇게 dropdown list로 만들었다고 한다. (일주일에 한 번씩 저 리스트를 바꾸는 것도 일일 듯.. -> 이건 나중에 자동화로 개선하기로..)
구글 시트에서는 '입고일+전시기한'을 적용하면 날짜가 계산되어 반영이 된다. 그런데 스마트시트는 문자로 인식을 하는지 그냥 문자로 붙여버린다.. 4/614 와 같이.
스마트시트는 날짜 계산을 하려면 항목을 날짜 타입으로 해야 한다. 그래서, 별도의 날짜 타입 열을 만든 후 수식을 적용하여 날짜로 변환을 했다.
적용한 수식사용한 함수는 DATE, VALUE, LEFT, RIGHT, FIND를 사용했다.
대략의 로직은 입고일 '4/6'을 '/'를 기준으로(FIND) 좌우를 잘라낸 후(LEFT, RIGHT) 숫자로 바꾼 뒤(VALUE) 날짜로 만들었다.(DATE)
주말 및 공휴일이 아닌 업무일로 날짜 지정하기
채권 상환 관련 업무를 스마트시트로 관리할 수 있도록 만들어준 적이 있다. 해당 시트에서는 채권의 만기일과 상환일을 automation을 통해 알람을 받도록 했다. 만기일은 원래 정해져 있는 날이어서 상관이 없는데 상환일은 조금 계산이 필요했다. 조건은 다음과 같았다.
만기일이 평일이면 만기일 = 상환일이지만, 주말이거나 공휴일이면 만기일 다음 영업일 = 상환일
다행히 스마트시트에는 각 시트별로 공휴일을 지정하는 기능이 있다. 이곳에 공휴일을 미리 넣어놓고 수식을 잘 만들면 계산이 가능할 듯싶었다. 먼저 공휴일을 설정해놓고..
요기서 공휴일 설정 가능만기일을 기준으로 다음과 같은 수식을 적용하였다. 주말과 공휴일을 구분해서 처리하기 위해 두 번의 IF문을 사용했다.
이렇게 적용로직은 대략 이렇다. 만약 만기일이 토요일, 일요일인 경우(WEEKDAY = 1 or 7)에는 만기일 다음 영업일(WORKDAY)을 상환일로 지정. 만약 만기일이 주중인데 공휴일이라면(만기일 전날 기준 +1 영업일이 만기일보다 크면 만기일이 공휴일이라고 판단) 그다음 영업일을 상환일로 지정한다.
조금 복잡해 보일 수 있는데 일단 동작은 잘하는 것을 확인했다.
매주 특정 요일의 날짜 표시하기
스마트시트로 업무 프로세스를 관리하다 보면 통계를 내야 하는 일이 종종 생긴다. 통계 생성 요청 중에 매주 월요일을 기준으로 수치를 보고 싶다는 요청이 있었다.
이 경우 통계 시트에서 월요일 날짜를 지정하고 그 날짜를 기준으로 수식이 동작하게 해야 한다. 그리고, 시간이 지나면서 해당 셀의 날짜가 이번 주 월요일 날짜로 반영이 돼야 했다.
어떻게 하면 오늘을 기준으로 특정 셀에 이번 주 월요일 날짜가 항상 보이도록 할지 고민을 하다가 아래와 같이 수식을 만들었다.
생각보다 간단오늘을 기준으로 WEEKDAY 숫자를 가져와서 원하는 요일만큼 빼주면 끝.
스마트시트를 오래 사용하다 보니 수식 관련 요청을 자주 받습니다. 나름 시행착오를 하며 노하우를 늘려가는 중인데 더 좋은 방법을 발견하신 분이 있으시면 댓글 남겨주세요. :)
