Excel 날짜에서 Unix 타임스탬프까지
Excel 날짜를 올바른 Unix 타임스탬프로 변환하는 방법을 아는 사람이 있습니까?
이것들 중 아무 것도 통하지 않았어...타임스탬프를 다시 환산하면 4년이 지났어
은 완벽하게했다: 이이 this this this this this this this this this this 。=(A2-DATE(1970,1,1))*86400
Credit : 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 셀로 Date 형식이고 Unix 타임스탬프가 A2 셀로 번호 형식이어야 한다고 가정할 경우 A2의 공식은 다음과 같습니다.
= (A1 * 86400) - 2209075200
여기서:
86400은 일 2209075200의 초수로 1900-01-01 ~1970-01-01 사이의 초수로 Excel 및 Unix 타임스탬프의 기본 날짜입니다.
위의 내용은 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 및 공간 구분 콘텐츠를 처리하는 데 자주 사용하는 것처럼 UNIX epoch를 시간대/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,입니다.) UNIX 에폭입니다.$1원하는 열/셀 번호를 나타내거나 변수를 대신 사용합니다.
에 의해, 「」, 「」에 됩니다.date 입수할 수 있는 는, GNU 를 할 수 2>/dev/null || date … +%%z 두 번째 '두 번째'는, $1GNU가 얼마나 일반적인지 고려할 때 BSD 버전을 가정하는 것은 권장하지 않습니다.
getline에 의해 .date +%ztz '아까운'으로 번역이 됩니다hours은 '먹다'처럼 될 거예요.-0700(PDT) 또는+0530(IST) 첫 번째 추출된 서브스트링은07 ★★★★★★★★★★★★★★★★★」05 번째가 '네'입니다00 ★★★★★★★★★★★★★★★★★」30 후 시간 됨) 및 세 사용('')tz인지 hours필요한 경우.
은 '보다 낫다'를 설정할 때 합니다.excel 대응 .hours/24.
Excel을 사용해야 2410725569（ 의 ( ( ( ( ( ）
임의의 비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@epoch를 것을 생각하면, unix epoch는 만한 @필수입니다. 다른 날짜 형식에는 9~10자리가 있으며, 이제 에폭과 시간대 오프셋의 두 가지 출력이 요구됩니다.따라서 예를 들어 를 사용할 수 있습니다. @1234567890입력으로 사용합니다.
버그
Lotus 1-2-3(원래 스프레드시트 소프트웨어)는 1900을 의도적으로 윤년으로 취급하지 않았습니다(이것에 의해, 모든 바이트가 카운트 되는 시점에서 코드 베이스가 감소했습니다).Microsoft Excel은 호환성을 위해 60일째(가칭 1900/02/29)를 건너뛰고 59일째부터 1900/02/28일까지의 Lotus 1-2-3 매핑을 유지했습니다.LibreOffice는 대신 60일을 1900/02/28로 할당하고 모든 이전 날을 1일로 되돌렸습니다.
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에서는 인식되지만 Libre Office에서는 인식되지 않습니다.
위의 편집이 거부되었기 때문에(여러분 중 실제로 시도한 사람이 있습니까?), 이 작업을 수행하기 위해 필요한 것은 다음과 같습니다.
Windows(및 Mac Office 2011+):
- 타임스탬프 = Unix "유닉스" =
(Excel Timestamp - 25569) * 86400 - 타임스탬프 = Excel 타임스탬프 =
(Unix Timestamp / 86400) + 25569
MAC OS X (Office 2011 이전):
- 타임스탬프 = Unix "유닉스" =
(Excel Timestamp - 24107) * 86400 - 타임스탬프 = Excel 타임스탬프 =
(Unix Timestamp / 86400) + 24107
하루가 지난 것 같은데 정확히 86만4천초야2209161600 번호 2209075200이 아닌 22091600 번호를 사용합니다.이 두 번호를 구글로 검색하면 위의 지원이 제공됩니다.저는 당신의 공식을 시도했지만 항상 제 서버와 하루가 다르게 나타납니다.인간의 시간이 아닌 unix로 생각하지 않는 한 unix 타임스탬프에서는 명확하지 않지만, 다시 한 번 확인하면 이것이 맞을 수 있습니다.
2010.03과 같은 "사람이 읽을 수 있는" 날짜가 있는 오래된 Excel 데이터베이스를 가지고 있었습니다.28 20:12:30 시일은 UTC+1(CET)로 되어 있어 에폭 시간으로 변환해야 합니다.
=(A4-DATE(1970;1;1)*86400-3600 공식을 사용하여 날짜를 A열에서 B열 값으로 에폭 시간으로 변환했습니다.타임존 오프셋을 확인하고 계산해보세요. 1시간은 3600초입니다.
제가 여기에 5년 이상 된 토픽을 쓴 유일한 이유는 이 토픽의 새로운 Excel 버전과 빨간색 게시물을 사용하고 있다는 것입니다만, 틀렸습니다.날짜(1970;1;1) 여기서 1970년과 1월은 가 아닌 와 분리될 필요가 있다.
이 문제도 발생하고 있다면 도움이 되길 바랍니다.좋은 하루 되세요 :)
데이터가 Unix 측에서 다음과 같은 형식으로 되어 있었기 때문에 현재 답변 중 어느 것도 효과가 없었습니다.
2016-02-02 19:21:42 UTC
Epoch 타임스탬프가 있는 다른 데이터를 참조할 수 있도록 Epoch로 변환해야 했습니다.
날짜 부분에 대한 새 열을 만들고 이 수식을 사용하여 구문 분석합니다.
=DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4))다른 Grendler가 이미 여기에 언급했듯이 다른 컬럼을 만듭니다.
=(B2-DATE(1970,1,1))*86400합계 초수를 얻기 위한 시간만을 더한 다른 열을 만듭니다.
=(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2)))마지막 두 열을 합산하는 마지막 열을 만듭니다.
=C2+D2
서머타임(3월 마지막 일요일부터 10월 마지막 일요일까지)을 보충하기 위해 다음 공식을 사용해야 했습니다.
=IF(
AND(
A2>=EOMONTH(DATE(YEAR(A2);3;1);0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR(A2);3;1);0);11);7);
A2<=EOMONTH(DATE(YEAR(A2);10;1);0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR(A2);10;1);0);11);7)
);
(A2-DATE(1970;1;1)-TIME(1;0;0))*24*60*60*1000;
(A2-DATE(1970;1;1))*24*60*60*1000
)
간단한 설명:
["A2"] 날짜가 3월의 마지막 일요일과 10월의 마지막 일요일[세 번째와 네 번째 코드 라인] 사이라면, 나는 그 날짜에 대해 한 시간을 빼야 한다[-TIME (1;0;0)].
커스텀 함수(User Defined Function : UDF)를 작성할 수 있는 경우:VBA 모듈을 생성하여 다음 코드를 복사합니다.
Function unix_time(Optional my_year, Optional my_month, Optional my_day, Optional my_hour, Optional my_minute, Optional my_second)
Dim now_date, now_time, ref_date, ref_time
my_year = IIf(IsMissing(my_year) = False, my_year, year(Now))
my_month = IIf(IsMissing(my_month) = False, my_month, month(Now))
my_day = IIf(IsMissing(my_day) = False, my_day, day(Now))
my_hour = IIf(IsMissing(my_hour) = False, my_hour, hour(Now))
my_minute = IIf(IsMissing(my_minute) = False, my_minute, minute(Now))
my_second = IIf(IsMissing(my_second) = False, my_second, second(Now))
now_date = DateSerial(my_year, my_month, my_day)
now_time = TimeSerial(my_hour, my_minute, my_second)
ref_date = DateSerial(1970, 1, 1)
ref_time = TimeSerial(0, 0, 0)'You can change this line based on your time zone. for example : Tehran -> TimeSerial(3,30,0)
now_date = now_date + now_time
ref_date = ref_date + ref_time
unix_time = (now_date - ref_date) * 86400
End Function
그런 다음 원하는 셀을 입력합니다.=unix_time()
위의 코드는 현재 날짜와 시간을 unix 형식으로 반환합니다.커스텀 날짜와 시간이 필요한 경우 6개의 파라미터를 "unix_time()" 함수에 전달합니다.
예:=unix_time(year, month, day, hour, minute, second)
여기 Javascript 구현이 있습니다.
// Parses an Excel Date ("serial") into a corresponding javascript Date in UTC+0 timezone.
// (with time equal to 00:00)
//
// 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".
//
export default function parseExcelDate(excelSerialDate, options) {
// https://support.microsoft.com/en-gb/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel
if (options && options.epoch1904) {
excelSerialDate += 1462
}
// "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, due to "leap seconds".
//
// Still, Javascript `Date` (and UNIX time in general) intentionally
// drops the concept of "leap seconds" in order to make things simpler.
// So it's fine.
// https://stackoverflow.com/questions/53019726/where-are-the-leap-seconds-in-javascript
//
// "The JavaScript Date object specifically adheres to the concept of Unix Time
// (albeit with higher precision). This is part of the POSIX specification,
// and thus is sometimes called "POSIX Time". It does not count leap seconds,
// but rather assumes every day had exactly 86,400 seconds. You can read about
// this in section 20.3.1.1 of the current ECMAScript specification, which states:
//
// "Time is measured in ECMAScript in milliseconds since 01 January, 1970 UTC.
// In time values leap seconds are ignored. It is assumed that there are exactly
// 86,400,000 milliseconds per day."
//
// The fact is, that the unpredictable nature of leap seconds makes them very
// difficult to work with in APIs. One can't generally pass timestamps around
// that need leap seconds tables to be interpreted correctly, and expect that
// one system will interpret them the same as another. For example, while your
// example timestamp 1483228826 is 2017-01-01T00:00:00Z on your system,
// it would be interpreted as 2017-01-01T00:00:26Z on POSIX based systems,
// or systems without leap second tables. So they aren't portable.
// Even on systems that have full updated tables, there's no telling what those
// tables will contain in the future (beyond the 6-month IERS announcement period),
// so I can't produce a future timestamp without risk that it may eventually change.
//
// To be clear - to support leap seconds in a programming language, the implementation
// must go out of its way to do so, and must make tradeoffs that are not always acceptable.
// Though there are exceptions, the general position is to not support them - not because
// of any subversion or active countermeasures, but because supporting them properly is much,
// much harder."
//
// https://en.wikipedia.org/wiki/Unix_time#Leap_seconds
// https://en.wikipedia.org/wiki/Leap_year
// https://en.wikipedia.org/wiki/Leap_second
//
const hour = 60 * 60 * 1000
return new Date(Math.round((excelSerialDate - daysBeforeUnixEpoch) * 24 * hour))
}
언급URL : https://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp
'programing' 카테고리의 다른 글
| vim shell 명령 내에서 .bashrc 별칭을 사용하려면 어떻게 해야 합니까? (:!...) (0) | 2023.04.19 |
|---|---|
| Oracle의 행과 동등SQL Server의 ID (0) | 2023.04.13 |
| 왜 "while(!feof(file)"은 항상 잘못된 것일까요? (0) | 2023.04.13 |
| SQL DROP TABLE 외부 키 제약 조건 (0) | 2023.04.13 |
| 여러 개의 목록 보기를 뷰 모델에 바인딩하려면 어떻게 해야 합니까? (0) | 2023.04.13 |