I am trying to calculate the serial number from a date value in javascript.
Basically I am looking for a equivalent javascript implementation for =DATEVALUE of google sheets.
I see that the base date for Google sheet is 30/12/1899.
To my understanding DATEVALUE is the number of days passed since the start date.
So I am calculating the logic like below
let baseDate = +new Date(1899, 12, 30);
let dateVal = new Date(inputDate); ("30/11/2024")
let millisecondsDifference= dateVal - baseDate;
let daysSinceStart = Math.floor(millisecondsDifference / (24 * 60 * 60 * 1000));
But this logic is not giving me the correct serial number when I entered in the sheet to convert it vice versa.
Eg: For 12/07/2026 – the output of above logic is 46332.
If we format 46332 as date in Google Sheets, it converts to 11/6/2026.
2
There are two reasons for the discrepancy:
1. Date()
The JavaScript Date() constructor takes the expected integers for year and day but, bafflingly, a monthIndex from 0 to 11 (not 1 to 12) for the month.
This means that new Date(1899, 12, 30) isn’t the date you expect. You should use:
let baseDate = new Date(1899, 11, 30); // 11, not 12!
This will mostly fix your code. However:
2. Daylight saving
Because of daylight saving hours, summer months are one hour ‘behind’. Since the ‘base date’ is in January, this means that summer dates between the ‘daylight saving days’ will be an hour short of what your calculation expects, so your Math.Floor() function will pull those dates back one day.
Therefore, use Math.round() instead.
With these two changes, 7th December 2026 correctly converts to 46363 while 6th November 2026 converts to 46332. The full code for the latter is:
let baseDate = new Date(1899, 11, 30);
let dateVal = new Date(2026, 10, 6)
let millisecondsDifference = dateVal - baseDate;
let daysSinceStart = Math.round(millisecondsDifference / (24 * 60 * 60 * 1000))
alert(daysSinceStart)
You can test this at https://jsfiddle.net/ .
PS – ‘summer’ above means ‘Northern Hemisphere summer’!
PPS – beware that this logic will fail for dates before March 1900, owing to Excel’s (deliberate) “Lotus-1-2-3” bug, whereby February 29th 1900 (which was not a real date) was included in the calendar.