Skip to content

Instantly share code, notes, and snippets.

@ShenTengTu
Created August 8, 2017 01:43
Show Gist options
  • Save ShenTengTu/14480ba606960a7abab4539c44866942 to your computer and use it in GitHub Desktop.
Save ShenTengTu/14480ba606960a7abab4539c44866942 to your computer and use it in GitHub Desktop.
Excel Date value ( DATEVALUE() ) Calculation in javascript
//Calculate Excel Date value
// Date 1899/12/31 serial number is 1
console.log(getExcelDateValue(new Date(1400,5,8)));//1400/6/8
console.log(getExcelDateValue(new Date(1899,11,31)));//1899/12/31
console.log(getExcelDateValue(new Date()));//now
function getExcelDateValue(dateObj){
var year = dateObj.getFullYear();
var month = dateObj.getMonth();
var date = dateObj.getDate();
var days = 1;//1899/12/31
//days in the the month
days += date;
//days of months in the year
while(month > 0){
let d31 = [1,3,5,7,8,10,12];
let d30 = [4,6,9,11];
if(d31.includes(month)){
days += 31;
}else if(d30.includes(month)){
days += 30;
}else{
days += isLeapYear(year) ? 29:28;
}
month -= 1;
}
if(year > 1899){///days from 1900 to the year before last
year -= 1
while(year > 1899){
days += isLeapYear(year) ? 366:365;
year -= 1;
}
}else{///days before 1900
let y = 1900
while(year < y){
days -= isLeapYear(y) ? 366:365;
y -= 1;
}
}
return days;
function isLeapYear(year){
let m400= year%400;
let m100= year%100;
let m4= year%4;
return (m400 === 0 || ( m4 === 0 && m100 !== 0));
}
}
@ShenTengTu
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment