excel-date-handlers.js

//ver 1.0.7
export const excelDateToJSDate = (date:number):Date => {
    //takes a number and return javascript Date object
  return new Date(Math.round((date - 25569)*86400*1000));
}

export const jsDateToExcelDate = (date:Date):number => {
    //takes javascript a Date object to an excel number
  let returnDateTime = 25569.0 + ((date.getTime()-(date.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
  return Math.floor(returnDateTime)
}

export const excelDateToStringDateFormat = (_int:number, _dateFormat:string):string => {
    //take an excel number and return a string-formatted date
  let jsDate = excelDateToJSDate(_int);
  switch(_dateFormat.toLowerCase()){
      case 'yyyy-mm-dd':
         return jsDate.getFullYear().toString()+"-"+("0"+(jsDate.getMonth()+1).toString()).slice(-2)+"-"+("0"+jsDate.getDate().toString()).slice(-2);
      case 'yyyy/mm/dd':
         return jsDate.getFullYear().toString()+"/"+("0"+(jsDate.getMonth()+1).toString()).slice(-2)+"/"+("0"+jsDate.getDate().toString()).slice(-2);
      case 'mm-dd-yyyy':
         return ("0"+(jsDate.getMonth()+1).toString()).slice(-2)+"-"+("0"+jsDate.getDate().toString()).slice(-2)+'-'+jsDate.getFullYear().toString();
      case 'mm/dd/yyyy':
         return ("0"+(jsDate.getMonth()+1).toString()).slice(-2)+"/"+("0"+jsDate.getDate().toString()).slice(-2)+'/'+jsDate.getFullYear().toString();
      default:
         throw new Error("format not matching")
  }
}

export const addDates = (dateInYYYYMMDDformatStringFormat:string, numberOfDate:number, _dateFormat:string):string => {
  //take "20200510" returns "20200511"
  let startingDateYYYYMMDDwithSlash = dateInYYYYMMDDformatStringFormat.replace(/(\d{4})(\d{2})(\d{2})/g, '$1-$2-$3');
  let jsDate = new Date(startingDateYYYYMMDDwithSlash);
  let excelDate = jsDateToExcelDate(jsDate);
  let excelDatePlusOne = excelDate+numberOfDate;
  let jsDatePlusOne = excelDateToJSDate(excelDatePlusOne);
  switch(_dateFormat){
      case 'yyyy-mm-dd':
        return jsDatePlusOne.getFullYear().toString()+"-"+("0"+(jsDatePlusOne.getMonth()+1).toString()).slice(-2)+"-"+("0"+jsDatePlusOne.getDate().toString()).slice(-2)
      case 'yyyymmdd':
        return jsDatePlusOne.getFullYear().toString()+("0"+(jsDatePlusOne.getMonth()+1).toString()).slice(-2)+("0"+jsDatePlusOne.getDate().toString()).slice(-2)
      default:
        throw new Error("format not matching");
  }
};