-
-
Notifications
You must be signed in to change notification settings - Fork 550
/
date.ts
44 lines (39 loc) · 1.86 KB
/
date.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/**
* Copyright 2023-present DreamNum Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
export const DEFFAULT_DATE_FORMAT = 'yyyy/mm/dd';
/**
* Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
*
* Excel has a leap year error in 1900. February 29, 1900 is considered a legal date. In fact, there is no February 29 in 1900.
* 1900.2.28 Date Serial 59
* 1900.2.29 Date Serial 61
* 1900.3.1 Date Serial 61
* 1901.1.1 Date Serial 367
* @param date
* @returns
*/
export function excelDateSerial(date: Date): number {
const baseDate = new Date(Date.UTC(1900, 0, 1)); // January 1, 1900, UTC
const leapDayDate = new Date(Date.UTC(1900, 1, 28)); // February 28, 1900, UTC
const dateInUTC = Date.UTC(date.getFullYear(), date.getMonth(), date.getDate());
// Calculate the difference in days between the base date and the input date
let dayDifference = (dateInUTC - baseDate.getTime()) / (1000 * 3600 * 24);
// If the date is later than February 28, 1900, the day difference needs to be adjusted to account for Excel errors
if (dateInUTC > leapDayDate.getTime()) {
dayDifference += 1;
}
return Math.floor(dayDifference) + 1; // Excel serial number starts from 1
}