Created
August 8, 2017 01:43
-
-
Save ShenTengTu/14480ba606960a7abab4539c44866942 to your computer and use it in GitHub Desktop.
Excel Date value ( DATEVALUE() ) Calculation in javascript
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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)); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
see in JSFiddle :
https://jsfiddle.net/ShenTengTu/1h66aoef/