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?

CodePudding user response：

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}`);
    //...
}

CodePudding user response：

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: