[excel] Unix 타임 스탬프에 대한 Excel 날짜

다음은 Microsoft Excel과 같은 스프레드 시트 시스템에 대해 UTC 를 가정 한 참조 용 매핑입니다 .

                         Unix  Excel Mac    Excel    Human Date  Human Time
Excel Epoch       -2209075200      -1462        0    1900/01/00* 00:00:00 (local)
Excel ≤ 2011 Mac† -2082758400          0     1462    1904/12/31  00:00:00 (local)
Unix Epoch                  0      24107    25569    1970/01/01  00:00:00 UTC
Example Below      1234567890      38395.6  39857.6  2009/02/13  23:31:30 UTC
Signed Int Max     2147483648      51886    50424    2038/01/19  03:14:08 UTC

One Second                  1       0.0000115740…             —  00:00:01
One Hour                 3600       0.0416666666…             ―  01:00:00
One Day                 86400          1        1             ―  24:00:00

_{*   ‘Jan Zero, 1900’은 1899/12/31입니다. 아래의 버그 섹션을 참조하십시오 . † Mac 용 Excel 2011 (및 이전 버전)은 1904 날짜 체계를 사용합니다 .}

 

CSV 및 공백으로 구분 된 콘텐츠 awk를 처리 하는 데 자주 사용 하면서 UNIX 시대를 시간대 / DST에 적합한 Excel 날짜 형식 으로 변환하는 방법을 개발했습니다 .

echo 1234567890 |awk '{
  # tries GNU date, tries BSD date on failure
  cmd = sprintf("date -d@%d +%%z 2>/dev/null || date -jf %%s %d +%%z", $1, $1)
  cmd |getline tz                                # read in time-specific offset
  hours = substr(tz, 2, 2) + substr(tz, 4) / 60  # hours + minutes (hi, India)
  if (tz ~ /^-/) hours *= -1                     # offset direction (east/west)
  excel = $1/86400 + hours/24 + 25569            # as days, plus offset
  printf "%.9f\n", excel
}'

내가 사용 echo이 예를 들어,하지만 당신은 파이프 첫 번째 열 (.CSV 형식의 첫 번째 셀에 대한이 같은 호출 파일을 할 수있는 awk -F,) 유닉스 시대입니다. $1원하는 열 / 셀 번호를 나타내도록 변경 하거나 대신 변수를 사용하십시오.

이렇게하면에 대한 시스템 호출이 생성됩니다 date. GNU 버전을 안정적으로 사용하려면 2>/dev/null || date … +%%z및 두 번째 , $1. GNU가 얼마나 일반적인지를 감안할 때 BSD 버전을 가정하지 않는 것이 좋습니다.

는 로 getline출력 된 표준 시간대 오프셋을 읽은 다음로 변환됩니다 . 형식과 같은 것 ( PDT ) 또는 ( IST 추출 제 문자열이되도록) 나 , 제는 또는 (다음 시간에 표현하는 60로 나눈 값)과의 세 번째 사용 되는 우리의 오프셋 여부 보는 음극과 달라져date +%ztzhours-0700+053007050030tzhours 필요한 경우.

이 페이지의 다른 모든 답변에 제공된 공식은을 설정하는 데 사용되며 excel일광 절약 시간 인식 시간대 조정을 hours/24.

이전 버전의 Mac 용 Excel을 사용 24107하는 경우 대신 다음 을 사용해야합니다 .25569 (위의 매핑 참조).

임의의 비 Epoch 시간을 GNU 날짜를 사용하여 Excel에 적합한 시간으로 변환하려면 :

echo "last thursday" |awk '{
  cmd = sprintf("date -d \"%s\" +\"%%s %%z\"", $0)
  cmd |getline
  hours = substr($2, 2, 2) + substr($2, 4) / 60
  if ($2 ~ /^-/) hours *= -1
  excel = $1/86400 + hours/24 + 25569
  printf "%.9f\n", excel
}'

이것은 기본적으로 동일한 코드이지만 date -d더 이상 @유닉스 시대를 나타내는 데 더 이상 필요하지 않습니다 (문자열 파서가 얼마나 능력이 있는가를 감안할 때, 실제로 @는 필수입니다; 다른 날짜 형식에 9-10 자리가 있습니까?). 위한 두 개의 출력 : 에포크 및 시간대 오프셋. 따라서 예 @1234567890를 들어 입력으로 사용할 수 있습니다 .

곤충

Lotus 1-2-3 (원래 스프레드 시트 소프트웨어)은 1900 년 이 아니 었음에도 불구하고 의도적 으로 1900 년을 윤년 으로 취급 했습니다 (이는 모든 바이트를 계산할 때 코드베이스를 줄였습니다). Microsoft Excel 은 호환성을 위해이 버그를 유지 하여 60 일 (가상 1900/02/29)을 건너 뛰고 Lotus 1-2-3의 59 일을 1900/02/28 일로 매핑했습니다. LibreOffice는 대신 60 일을 1900/02/28로 할당하고 이전의 모든 날을 뒤로 밀었습니다.

1900/03/01 이전의 모든 날짜는 쉬는 날과 같을 수 있습니다.

Day        Excel   LibreOffice
-1            -1    1899/12/29
 0    1900/01/00*   1899/12/30
 1    1900/01/01    1899/12/31
 2    1900/01/02    1900/01/01
 …
59    1900/02/28    1900/02/27
60    1900/02/29(!) 1900/02/28
61    1900/03/01    1900/03/01

Excel은 음수 날짜를 인식하지 않으며 0 일에 대한 1 월 0 일 (1899/12/31)에 대한 특별한 정의가 있습니다. 내부적으로 Excel은 실제로 음수 날짜를 처리하지만 (결국 숫자 일뿐) 날짜로 표시하는 방법을 모르기 때문에 숫자로 표시합니다 (이전 날짜를 음수로 변환 할 수도 없음). 일이 없었던 1900 년 2 월 29 일은 Excel에서는 인식되지만 LibreOffice에서는 인식되지 않습니다.