Javascript를 사용하여 Excel 날짜 일련 번호를 현재 날짜로 변환
날짜(문자열)를 Microsoft Excel에서 사용되는 날짜 일련 번호로 변환하는 다음과 같은 Javascript 코드가 있습니다.
function JSDateToExcelDate(inDate) {
var returnDateTime = 25569.0 + ((inDate.getTime() - (inDate.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
return returnDateTime.toString().substr(0,5);
}
그럼 어떻게 그 반대로 하지?(Microsoft Excel에서 사용되는 날짜 일련 번호를 날짜 문자열로 변환하는 Javascript 코드입니까?
이것을 시험해 보세요.
function ExcelDateToJSDate(serial) {
var utc_days = Math.floor(serial - 25569);
var utc_value = utc_days * 86400;
var date_info = new Date(utc_value * 1000);
var fractional_day = serial - Math.floor(serial) + 0.0000001;
var total_seconds = Math.floor(86400 * fractional_day);
var seconds = total_seconds % 60;
total_seconds -= seconds;
var hours = Math.floor(total_seconds / (60 * 60));
var minutes = Math.floor(total_seconds / 60) % 60;
return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}
커스텀 메이드:)
당신을 위해 한 마디 했어요.
function ExcelDateToJSDate(date) {
return new Date(Math.round((date - 25569)*86400*1000));
}
단답(일자 1900-02-28)
new Date(Date.UTC(0, 0, excelSerialDate - 1));
단답(일자 <= 1900-02-28)
new Date(Date.UTC(0, 0, excelSerialDate));
기능하는 이유
@은 매우 에 들었습니다.효과가 , @legett의 @SteveR에 대해서는 좀 더 .대부분이 동작하고 있지만, 그 방법을 이해하기 위해서 조금 더 깊이 파고들고 싶었습니다.Date.UTC()일
주의: 특히 오래된 날짜(1970년 이전)의 경우 시간대 오프셋에 문제가 있을 수 있습니다.브라우저, 시간대, Chrome 67 오류(시간대 이력 변경)를 참조하기 때문에 가능하면 시간 이동에 의존하지 않고 UTC에 머무르고 싶습니다.
Excel 날짜는 1900년 1월 1일을 기준으로 한 정수입니다(PC에서는 1904년 1월 1일을 기준으로 합니다).PC에 있다고 가정해 봅시다.
1900-01-01 is 1.0
1901-01-01 is 367.0, +366 days (Excel incorrectly treats 1900 as a leap year)
1902-01-01 is 732.0, +365 days (as expected)
는 JS를 기반으로 .Jan 1st 1970 UTC를 . . . .를 사용하면Date.UTC(year, month, ?day, ?hour, ?minutes, ?seconds)UTC(UTC로 하다그것은 우리에게 도움이 되는 몇 가지 흥미로운 기능을 가지고 있다.
의 Date.UTC()을 제외하고 0을 .day. 을 다른 또는 이러한 범위를 벗어나는 숫자를 받아들이고 입력을 다른 파라미터의 오버플로 또는 언더플로로 변환합니다.
Date.UTC(1970, 0, 1, 0, 0, 0, 0) is 0ms
Date.UTC(1970, 0, 1, 0, 0, 0, 1) is 1ms
Date.UTC(1970, 0, 1, 0, 0, 1, 0) is 1000ms
1970년 1월 1일 이전 날짜도 가능합니다.여기서는 요일을 0에서1로 줄이고 시간, 분, 초 및 밀리초를 늘립니다.
Date.UTC(1970, 0, 0, 23, 59, 59, 999) is -1ms
0~99년에서 1900~1999년까지의 연수를 변환할 수 있을 정도로 스마트합니다.
Date.UTC(70, 0, 0, 23, 59, 59, 999) is -1ms
1900-01-01을 어떻게 표현해야 할까요?원하는 날짜에 대한 출력을 보다 쉽게 보려면
new Date(Date.UTC(1970, 0, 1, 0, 0, 0, 0)).toISOString() gives "1970-01-01T00:00:00.000Z"
new Date(Date.UTC(0, 0, 1, 0, 0, 0, 0)).toISOString() gives "1900-01-01T00:00:00.000Z"
이제 우리는 타임존을 처리해야 합니다.Excel의 날짜 표현에는 표준 시간대 개념이 없지만 JS는 표준 시간대 개념이 있습니다.이 문제를 해결하는 가장 쉬운 방법은 IMHO로 입력된 모든 Excel 날짜를 UTC로 간주하는 것입니다(가능한 경우).
Excel 날짜 732.0으로 시작합니다.
new Date(Date.UTC(0, 0, 732, 0, 0, 0, 0)).toISOString() gives "1902-01-02T00:00:00.000Z"
위의 윤년 문제로 인해 하루 정도 지연된 것으로 알고 있습니다.day 파라미터를 1만큼 줄여야 합니다.
new Date(Date.UTC(0, 0, 732 - 1, 0, 0, 0, 0)) gives "1902-01-01T00:00:00.000Z"
새로운 Date(년, 월, 일) 생성자를 사용하여 날짜를 작성하는 경우 파라미터는 로컬 시간대를 사용합니다.PT(UTC-7/UTC-8) 타임존에 있고,
new Date(1902, 0, 1).toISOString() gives me "1902-01-01T08:00:00.000Z"
유닛 테스트에서는
new Date(Date.UTC(1902, 0, 1)).toISOString() gives "1902-01-01T00:00:00.000Z"
Excel 시리얼 날짜를 js 날짜로 변환하는 Typescript 함수는 다음과 같습니다.
public static SerialDateToJSDate(excelSerialDate: number): Date {
return new Date(Date.UTC(0, 0, excelSerialDate - 1));
}
사용할 UTC 날짜를 추출하려면
public static SerialDateToISODateString(excelSerialDate: number): string {
return this.SerialDateToJSDate(excelSerialDate).toISOString().split('T')[0];
}
사양:
Excel은 날짜를 순차적인 일련번호로 저장하여 계산에 사용할 수 있도록 합니다.1900년 1월 1일은 시리얼 번호1이고 2008년 1월 1일은 시리얼 번호39448입니다1900년 1월 1일 이후 39,447일이기 때문입니다.
- 또한 https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year도 이용하실 수 있습니다.
Microsoft Multiplan과 Microsoft Excel이 출시되었을 때도 1900년은 윤년이라고 가정했습니다.이러한 가정 하에 Microsoft Multiplan 및 Microsoft Excel은 Lotus 1-2-3에서 사용하는 동일한 일련 날짜 시스템을 사용할 수 있으며 Lotus 1-2-3과 더 나은 호환성을 제공할 수 있습니다.1900년을 윤년으로 간주하면 사용자가 한 프로그램에서 다른 프로그램으로 워크시트를 쉽게 이동할 수 있습니다.
시간은 1970년 1월1일 UTC 이후 ECMAScript로 밀리초 단위로 측정됩니다.시간 값 윤초는 무시됩니다.하루에 정확히 86,400,000 밀리초가 있다고 가정합니다.
- https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date#Unix_timestamp
new Date(value)1970년 1월1일 00:00:00 UTC(유닉스 에폭) 이후의 밀리초수를 나타내는 정수값. 윤초는 무시됩니다.대부분의 Unix Timestamp 함수는 가장 가까운 1초까지만 정확합니다.
종합:
function xlSerialToJsDate(xlSerial){
// milliseconds since 1899-12-31T00:00:00Z, corresponds to Excel serial 0.
var xlSerialOffset = -2209075200000;
var elapsedDays;
// each serial up to 60 corresponds to a valid calendar date.
// serial 60 is 1900-02-29. This date does not exist on the calendar.
// we choose to interpret serial 60 (as well as 61) both as 1900-03-01
// so, if the serial is 61 or over, we have to subtract 1.
if (xlSerial < 61) {
elapsedDays = xlSerial;
}
else {
elapsedDays = xlSerial - 1;
}
// javascript dates ignore leap seconds
// each day corresponds to a fixed number of milliseconds:
// 24 hrs * 60 mins * 60 s * 1000 ms
var millisPerDay = 86400000;
var jsTimestamp = xlSerialOffset + elapsedDays * millisPerDay;
return new Date(jsTimestamp);
}
원라이너로서:
function xlSerialToJsDate(xlSerial){
return new Date(-2209075200000 + (xlSerial - (xlSerial < 61 ? 0 : 1)) * 86400000);
}
한 줄까지 계산하지 않아도 됩니다.
// serialDate is whole number of days since Dec 30, 1899
// offsetUTC is -(24 - your timezone offset)
function SerialDateToJSDate(serialDate, offsetUTC) {
return new Date(Date.UTC(0, 0, serialDate, offsetUTC));
}
때문에 UTC-0700 PST를 했습니다.offsetUTC = -1700:00 (24 - 7 = 17)
이 기능은 Google 시트에서 일련 형식으로 날짜를 읽을 때도 유용합니다.이 문서에서는 시리얼에 하루의 일부를 나타내는 소수점을 사용할 수 있음을 나타내고 있습니다.
날짜, 시간, 날짜 및 기간 필드를 Lotus 1-2-3에서 공용하는 "일련 번호" 형식으로 두 배로 출력하도록 지시합니다.값의 정수 부분(10진수 왼쪽)은 1899년 12월 30일 이후의 날짜를 카운트합니다.소수점 부분(소수점 오른쪽)은 시간을 하루의 일부로 카운트합니다.예를 들어 1900년 1월 1일 정오에는 2.5, 1899년 12월 30일 이후 2일, 정오에는 반나절이 되기 때문에 0.5가 됩니다.1900년 2월 1일 오후 3시 33.625분입니다.그러면 1900년이 윤년이 아닌 것으로 올바르게 취급됩니다.
따라서 일련 번호를 소수점까지 지원하려면 일련 번호를 구분해야 합니다.
function SerialDateToJSDate(serialDate) {
var days = Math.floor(serialDate);
var hours = Math.floor((serialDate % 1) * 24);
var minutes = Math.floor((((serialDate % 1) * 24) - hours) * 60)
return new Date(Date.UTC(0, 0, serialDate, hours-17, minutes));
}
Gil의 답변은 단순해서 정말 마음에 들었지만, 시간대 오프셋이 부족했다.자, 여기 있습니다.
function date2ms(d) {
let date = new Date(Math.round((d - 25569) * 864e5));
date.setMinutes(date.getMinutes() + date.getTimezoneOffset());
return date;
}
이 논의가 시작된 지 몇 년이 지나서야 우연히 이 문제에 대한 간단한 해답을 얻을 수 있을 것 같습니다.fwiw, Excel에서 필요한 JS 날짜로 변환한 방법은 다음과 같습니다.
var exdate = 33970; // represents Jan 1, 1993
var e0date = new Date(0); // epoch "zero" date
var offset = e0date.getTimezoneOffset(); // tz offset in min
// calculate Excel xxx days later, with local tz offset
var jsdate = new Date(0, 0, exdate-1, 0, -offset, 0);
jsdate.toJSON() => '1993-01-01T00:00:00.000Z'
기본적으로 Excel 일수(1-based)를 더하고 음의 로컬 시간대 오프셋에 따라 분을 조정하여 계산된 새로운 날짜 개체를 작성합니다.
같은 문제가 있어서 몇 가지 해결 방법이 떠올랐지만 로케일, 시간대 등에 문제가 생기기 시작했지만 결국 필요한 정밀도를 추가할 수 있었습니다.
toDate(serialDate, time = false) {
let locale = navigator.language;
let offset = new Date(0).getTimezoneOffset();
let date = new Date(0, 0, serialDate, 0, -offset, 0);
if (time) {
return serialDate.toLocaleTimeString(locale)
}
return serialDate.toLocaleDateString(locale)
}
함수의 '시간' 인수는 전체 날짜를 표시할지 아니면 날짜 시간만 표시할지 선택합니다.
@silkfire의 솔루션 감사합니다!
확인 후.동구에서는 @silkfire가 정답이라는 것을 알게 되었습니다.서반구는 정반대입니다.
따라서 타임존에 대해서는 아래를 참조해 주십시오.
function ExcelDateToJSDate(serial) {
// Deal with time zone
var step = new Date().getTimezoneOffset() <= 0 ? 25567 + 2 : 25567 + 1;
var utc_days = Math.floor(serial - step);
var utc_value = utc_days * 86400;
var date_info = new Date(utc_value * 1000);
var fractional_day = serial - Math.floor(serial) + 0.0000001;
var total_seconds = Math.floor(86400 * fractional_day);
var seconds = total_seconds % 60;
total_seconds -= seconds;
var hours = Math.floor(total_seconds / (60 * 60));
var minutes = Math.floor(total_seconds / 60) % 60;
return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}
// 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);
};
@dartfire answer의 dart 구현
DateTime getDateFromSerialDay(double serial) {
final utc_days = (serial - 25569).floor();
final utc_value = utc_days * 86400;
final date_info = DateTime.fromMillisecondsSinceEpoch(utc_value * 1000);
final fractional_day = serial - utc_days + 0.0000001;
var total_seconds = (86400 * fractional_day).floor();
var seconds = total_seconds % 60;
total_seconds -= seconds;
var hours = (total_seconds / (60 * 60) % 24).floor();
var minutes = ((total_seconds / 60) % 60).floor();
return DateTime(date_info.year, date_info.month, date_info.day, hours,
minutes, seconds);
}
오래된 스레드입니다만, 이 npm 패키지를 작성하기 위해 준비했던 시간을 절약할 수 있으면 좋겠습니다.
$ npm install js-contract-date-date-date-module
패키지 사용:
const toExcelDate = require('js-excel-date-convert').toExcelDate;
const fromExcelDate = require('js-excel-date-convert').fromExcelDate;
const jul = new Date('jul 5 1998');
toExcelDate(jul); // 35981 (1900 date system)
fromExcelDate(35981); // "Sun, 05 Jul 1998 00:00:00 GMT"
이러한 결과는, https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system 의 예에서 확인할 수 있습니다.
코드:
function fromExcelDate (excelDate, date1904) {
const daysIn4Years = 1461;
const daysIn70years = Math.round(25567.5 + 1); // +1 because of the leap-year bug
const daysFrom1900 = excelDate + (date1904 ? daysIn4Years + 1 : 0);
const daysFrom1970 = daysFrom1900 - daysIn70years;
const secondsFrom1970 = daysFrom1970 * (3600 * 24);
const utc = new Date(secondsFrom1970 * 1000);
return !isNaN(utc) ? utc : null;
}
function toExcelDate (date, date1904) {
if (isNaN(date)) return null;
const daysIn4Years = 1461;
const daysIn70years = Math.round(25567.5 + 1); // +1 because of the leap-year bug
const daysFrom1970 = date.getTime() / 1000 / 3600 / 24;
const daysFrom1900 = daysFrom1970 + daysIn70years;
const daysFrom1904Jan2nd = daysFrom1900 - daysIn4Years - 1;
return Math.round(date1904 ? daysFrom1904Jan2nd : daysFrom1900);
}
이 조작 방법에 대해서는, https://bettersolutions.com/excel/dates-times/1904-date-system.htm 를 참조해 주세요.
언급URL : https://stackoverflow.com/questions/16229494/converting-excel-date-serial-number-to-date-using-javascript
'codememo' 카테고리의 다른 글
| 유형 간에 변환할 수 있는 변환기를 찾을 수 없습니다. (0) | 2023.04.13 |
|---|---|
| VBA enum에서 "Constant Expression Required" 오류가 발생할 수 있음 (0) | 2023.04.13 |
| 눈금 레이블 글꼴 크기 축소 (0) | 2023.04.13 |
| 파일 타입과 iPhone 어플리케이션을 관련짓는 방법 (0) | 2023.04.13 |
| 오리진 마스터 분기의 파일 버전으로 되돌리는 방법 (0) | 2023.04.13 |