0
130
VistasHow do I convert a JS date to excel date?
I am trying to convert a JS date, be it in any format:
25/11/2021
25-11-2021
11/25/2021....
to it's Excel equivalent date number. My code to convert the date is as follows:
function JSDateToExcelDate(inDate) {
let date = new Date(inDate);
var returnDateTime = 25569.0 + ((date.getTime() - (date.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
return returnDateTime.toString().substr(0,5);
}
When I pass a string date (for eg: JSDateToExcelDate("25/11/2021")) into the above mentioned function, I get the result as NaN. How do I get the desired result?
In other words, what is the DATEVALUE("25/11/2021") equivalent of excel in javascript?
·
Juan Pablo Isaza
2 Respuestas
Responde la pregunta0
IDK How Excel DATEVALUE Function formula works, BUT I do know that new Date(datestring) take an argument datestring string respect date format of "YYYY-MM-dd"!
function JSDateToExcelDate(inDate) {
let [dd,MM,YYYY] = inDate.split('/');
let date = new Date(`${YYYY}-${MM}-${dd}`);
//...
}
·
Juan Pablo Isaza
Denunciar
0
Here's what you're looking for. You'll realize that I've used the base date value of 1899-11-30 which seems to be preferred in this case.
// Date needs to come in format "YYYY-MM-DD"
function convertToExcelDate(date)
{
//This local function deals with date formatting only.
// - Here date may come in any format. YYYY-MM-DD or DD-MM-YYYY or "/" may be used
function _formatDate(date)
{
var formattedDate = date.replace(/\//g,"-");
var isRightFormat = formattedDate.indexOf("-") === 4; // True if date is in form YYYY-MM-DD
if(!isRightFormat)
{
var [DD, MM, YYYY] = formattedDate.split("-");
formattedDate = `${YYYY}-${MM}-${DD}`;
}
return formattedDate;
}
var _date = Date.parse(_formatDate(date));
return parseInt(Math.round((_date - new Date(1899,11,30)) / (1000 * 60 * 60 * 24)).toFixed(10));
}
function convertInput()
{
var _in = document.getElementById("input");
if(_in.value)
console.log(convertToExcelDate(_in.value));
}<input type="text" id="input" />
<button id="convert-button" onClick="convertInput();">Convert</button>Tests done with the following values:
·
Juan Pablo Isaza
Denunciar
Responde la pregunta