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

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

---

누구든지 Excel 날짜를 올바른 Unix 타임 스탬프로 변환하는 방법을 알고 있습니까?

---

이것들 중 아무것도 나를 위해 일하지 않았습니다 ... 타임 스탬프를 다시 변환했을 때 4 년이 걸립니다.

이것은 완벽하게 작동했습니다. =(A2-DATE(1970,1,1))*86400

출처 : Filip Czaja http://fczaja.blogspot.ca

원본 게시물 : http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html

---

Windows 및 Mac Excel (2011) :

Unix Timestamp = (Excel Timestamp - 25569) * 86400
Excel Timestamp =  (Unix Timestamp / 86400) + 25569

MAC OS X (2007) :

Unix Timestamp = (Excel Timestamp - 24107) * 86400
Excel Timestamp =  (Unix Timestamp / 86400) + 24107

참고로 :

86400 = Seconds in a day
25569 = Days between 1970/01/01 and 1900/01/01 (min date in Windows Excel)
24107 = Days between 1970/01/01 and 1904/01/02 (min date in Mac Excel 2007)

---

Excel의 날짜가 날짜 형식의 A1 셀에 있고 Unix 타임 스탬프가 숫자 형식의 A2 셀에 있어야한다고 가정하면 A2의 수식은 다음과 같아야합니다.

= (A1 * 86400)-2209075200

어디:

86400은 하루의 초 수입니다. 2209075200은 Excel 및 Unix 타임 스탬프의 기본 날짜 인 1900-01-01과 1970-01-01 사이의 초 수입니다.

위의 내용은 Windows에 해당됩니다. Mac에서 Excel의 기본 날짜는 1904-01-01이고 초 숫자는 2082844800으로 수정되어야합니다.

---

다음은 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 doesn't acknowledge negative dates and has a special definition of the Zeroth of January (1899/12/31) for day zero. Internally, Excel does indeed handle negative dates (they're just numbers after all), but it displays them as numbers since it doesn't know how to display them as dates (nor can it convert older dates into negative numbers). Feb 29 1900, a day that never happened, is recognized by Excel but not LibreOffice.

---

Because my edits to the above were rejected (did any of you actually try?), here's what you really need to make this work:

Windows (And Mac Office 2011+):

• Unix Timestamp = (Excel Timestamp - 25569) * 86400
• Excel Timestamp = (Unix Timestamp / 86400) + 25569

MAC OS X (pre Office 2011):

• Unix Timestamp = (Excel Timestamp - 24107) * 86400
• Excel Timestamp = (Unix Timestamp / 86400) + 24107

---

You're apparently off by one day, exactly 86400 seconds. Use the number 2209161600 Not the number 2209075200 If you Google the two numbers, you'll find support for the above. I tried your formula but was always coming up 1 day different from my server. It's not obvious from the unix timestamp unless you think in unix instead of human time ;-) but if you double check then you'll see this might be correct.

---

I had an old Excel database with "human-readable" dates, like 2010.03.28 20:12:30 Theese dates were in UTC+1 (CET) and needed to convert it to epoch time.

I used the =(A4-DATE(1970;1;1))*86400-3600 formula to convert the dates to epoch time from the A column to B column values. Check your timezone offset and make a math with it. 1 hour is 3600 seconds.

The only thing why i write here an anwser, you can see that this topic is more than 5 years old is that i use the new Excel versions and also red posts in this topic, but they're incorrect. The DATE(1970;1;1). Here the 1970 and the January needs to be separated with ; and not with ,

If you're also experiencing this issue, hope it helps you. Have a nice day :)

---

None of the current answers worked for me because my data was in this format from the unix side:

2016-02-02 19:21:42 UTC

I needed to convert this to Epoch to allow referencing other data which had epoch timestamps.

1. Create a new column for the date part and parse with this formula

   =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
   

2. As other Grendler has stated here already, create another column

   =(B2-DATE(1970,1,1))*86400 
   

3. Create another column with just the time added together to get total seconds:

   =(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2)))
   

4. 마지막 두 열을 함께 추가하는 마지막 열을 만듭니다.

   =C2+D2
   

---

여기에 대한 나의 궁극적 인 대답이 있습니다.

또한 분명히 javascript의 new Date(year, month, day)생성자는 윤초도 고려하지 않습니다.

// Parses an Excel Date ("serial") into a
// corresponding javascript Date in UTC+0 timezone.
//
// Doesn't account for leap seconds.
// Therefore is not 100% correct.
// But will do, I guess, since we're
// not doing rocket science here.
//
// https://www.pcworld.com/article/3063622/software/mastering-excel-date-time-serial-numbers-networkdays-datevalue-and-more.html
// "If you need to calculate dates in your spreadsheets,
//  Excel uses its own unique system, which it calls Serial Numbers".
//
lib.parseExcelDate = function (excelSerialDate) {
  // "Excel serial date" is just
  // the count of days since `01/01/1900`
  // (seems that it may be even fractional).
  //
  // The count of days elapsed
  // since `01/01/1900` (Excel epoch)
  // till `01/01/1970` (Unix epoch).
  // Accounts for leap years
  // (19 of them, yielding 19 extra days).
  const daysBeforeUnixEpoch = 70 * 365 + 19;

  // An hour, approximately, because a minute
  // may be longer than 60 seconds, see "leap seconds".
  const hour = 60 * 60 * 1000;

  // "In the 1900 system, the serial number 1 represents January 1, 1900, 12:00:00 a.m.
  //  while the number 0 represents the fictitious date January 0, 1900".
  // These extra 12 hours are a hack to make things
  // a little bit less weird when rendering parsed dates.
  // E.g. if a date `Jan 1st, 2017` gets parsed as
  // `Jan 1st, 2017, 00:00 UTC` then when displayed in the US
  // it would show up as `Dec 31st, 2016, 19:00 UTC-05` (Austin, Texas).
  // That would be weird for a website user.
  // Therefore this extra 12-hour padding is added
  // to compensate for the most weird cases like this
  // (doesn't solve all of them, but most of them).
  // And if you ask what about -12/+12 border then
  // the answer is people there are already accustomed
  // to the weird time behaviour when their neighbours
  // may have completely different date than they do.
  //
  // `Math.round()` rounds all time fractions
  // smaller than a millisecond (e.g. nanoseconds)
  // but it's unlikely that an Excel serial date
  // is gonna contain even seconds.
  //
  return new Date(Math.round((excelSerialDate - daysBeforeUnixEpoch) * 24 * hour) + 12 * hour);
};

참고 URL : https://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp