I have an Electron app where an Excel-Sheet with a couple of columns containing time values needs to be imported. In my app those values are converted in a loop to momentjs object for further manipulation:

x['Time'] = moment(x['Time'], ['HH:mm','HH:mm:ss']).format('HH:mm:ss');

This works fine as long the Excel contains time values formatted as text. But if the Excel is set up the way it's meant to be, then the value of the Cell is a Number between 0 and 1 (Excel counts time internally as floating point - so e.g. 0,5 translates to 12:00:00).

Does anyone know how I can translate that back to a readable Timevalue for momentjs?

I have an Electron app where an Excel-Sheet with a couple of columns containing time values needs to be imported. In my app those values are converted in a loop to momentjs object for further manipulation:

x['Time'] = moment(x['Time'], ['HH:mm','HH:mm:ss']).format('HH:mm:ss');

This works fine as long the Excel contains time values formatted as text. But if the Excel is set up the way it's meant to be, then the value of the Cell is a Number between 0 and 1 (Excel counts time internally as floating point - so e.g. 0,5 translates to 12:00:00).

Does anyone know how I can translate that back to a readable Timevalue for momentjs?

• javascript
• excel
• time
• momentjs

Share Improve this question Follow edited Sep 29, 2018 at 6:59 benvc 15.2k44 gold badges3838 silver badges5757 bronze badges asked Sep 29, 2018 at 5:47 TorfTorf 1,2441212 silver badges3636 bronze badges 3

• How are you exporting the data from Excel? Are you using VBA? Or do you use a spreadsheet parser? – Sancarn Commented Sep 29, 2018 at 7:25
• I'm using the munity version of sheetjsfor import and export functions. – Torf Commented Sep 29, 2018 at 8:42
• This may help you – Sancarn Commented Sep 30, 2018 at 18:19

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 2

export const parseDateExcel = (excelTimestamp) => {
    const secondsInDay = 24 * 60 * 60;
    const excelEpoch = new Date(1899, 11, 31);
    const excelEpochAsUnixTimestamp = excelEpoch.getTime();
    const missingLeapYearDay = secondsInDay * 1000;
    const delta = excelEpochAsUnixTimestamp - missingLeapYearDay;
    const excelTimestampAsUnixTimestamp = excelTimestamp * secondsInDay * 1000;
    const parsed = excelTimestampAsUnixTimestamp + delta;
    return isNaN(parsed) ? null : parsed;
};

Usage:

new Date(parseDateExcel(36902.49097)) //=> Thu Jan 11 2001 11:46:59 GMT+0000 (Greenwich Mean Time)

Source

This is as far as I have work with the Excel time decimal values. So according to Excel the time text is represented by a decimal number ranging from 0 to 1.

function excelDateToJSDate(excel_date, time = false) {
  let day_time = excel_date % 1
  let meridiem = "AMPM"
  let hour = Math.floor(day_time * 24)
  let minute = Math.floor(Math.abs(day_time * 24 * 60) % 60)
  let second = Math.floor(Math.abs(day_time * 24 * 60 * 60) % 60)
  hour >= 12 ? meridiem = meridiem.slice(2, 4) : meridiem = meridiem.slice(0, 2)
  hour > 12 ? hour = hour - 12 : hour = hour
  hour = hour < 10 ? "0" + hour : hour
  minute = minute < 10 ? "0" + minute : minute
  second = second < 10 ? "0" + second : second
  let daytime = "" + hour + ":" + minute + ":" + second + " " + meridiem
  return time ? daytime : (new Date(0, 0, excel_date, 0, -new Date(0).getTimezoneOffset(), 0)).toLocaleDateString(navigator.language, {}) + " " + daytime
};

First we define the midday, then handle the hours, minutes and seconds, then verify if the given hour is either AM or PM, as a formatting fashion preference we change the 24 hours to 12 hour convention and add padding zeros to any value less than 10 and lastly return the time or date as a string.

Example

function excelDateToJSDate(excel_date, time = false) {
  let day_time = excel_date % 1
  let meridiem = "AMPM"
  let hour = Math.floor(day_time * 24)
  let minute = Math.floor(Math.abs(day_time * 24 * 60) % 60)
  let second = Math.floor(Math.abs(day_time * 24 * 60 * 60) % 60)
  hour >= 12 ? meridiem = meridiem.slice(2, 4) : meridiem = meridiem.slice(0, 2)
  hour > 12 ? hour = hour - 12 : hour = hour
  hour = hour < 10 ? "0" + hour : hour
  minute = minute < 10 ? "0" + minute : minute
  second = second < 10 ? "0" + second : second
  let daytime = "" + hour + ":" + minute + ":" + second + " " + meridiem
  return time ? daytime : (new Date(0, 0, excel_date, 0, -new Date(0).getTimezoneOffset(), 0)).toLocaleDateString(navigator.language, {}) + " " + daytime
};

console.log(excelDateToJSDate(0.125, true));
console.log(excelDateToJSDate(43556));

Due to the fact I could not find a real answer, here is one that worked for me:

let fromExcel = 0,709722222222222; //translates to 17:02:00
let basenumber = (fromExcel*24)
let hour = Math.floor(basenumber).toString();
if (hour.length < 2) {
    hour = '0'+hour;
}

var minute = Math.round((basenumber % 1)*60).toString();
if (minute.length < 2) {
 minute = '0'+minute;
}
let Timestring = (hour+':'+minute+':00');

So I have a String momentjscan translate. The reason I do not mark this as answer is that there sure are nicer ways of conversion and I could not find a solution to calculate the seconds (which in my special case does not matter, as I do not use them).