Search code examples
javascriptexceldateconstructoroffice-scripts

JS Date constructor is inconsistent for future dates

I am doing some date manipulation in Office Scripts in Excel, and I noticed a strange behavior with dates future vs. past--I don't know if anyone else gets similar results because I can't consistently recreate this. For example, when I run the following code:

let d1 = new Date("2023-12-16"); // some date in the future
let d2 = new Date("2023-1-1");   // some date in the past
console.log(d1);
console.log(d2);

I get this output:

"2023-12-16T00:00:00.000Z"
"2023-01-01T07:00:00.000Z"

I found some great information in the discussion about the JS Date constructor here, but I still don't understand. Why doesn't the date in the future infer my time zone like it does with the date in the past? Is this yet another issue this constructor has when parsing strings?

But when I run

let d6 = new Date("2023-9-14");
let d7 = new Date("2023-09-14");
console.log(d6);
console.log(d7);

I get

"2023-09-14T06:00:00.000Z"
"2023-09-14T00:00:00.000Z"

(which also appears to be off on the time zone...)

Does it have to do with the number of characters used to specify the month?

Solution

  • This isn't too difficult to explain: 2023-12-16 is a standard Javascript Date Time String Format, and 2023-1-1 is not.

    I like the summary provided by the MDN docs, which simply says:

    There are many ways to format a date as a string. The JavaScript specification only specifies one format to be universally supported: the date time string format, a simplification of the ISO 8601 calendar date extended format. The format is as follows:

    YYYY-MM-DDTHH:mm:ss.sssZ

    The longer and more technical description in the ECMA-262 standard (see 21.4.3.2 Date.parse), says, in part1:

    The String may be interpreted as a local time, a UTC time, or a time in some other time zone, depending on the contents of the String. The function first attempts to parse the String according to the format described in Date Time String Format (21.4.1.18), including expanded years. If the String does not conform to that format the function may fall back to any implementation-specific heuristics or implementation-specific date formats.

    In short, when you pass a string which adheres to the Data Time String Format to the Date constructor, then it will be parsed in a very specific way, including using the UTC (commonly known as GMT) time zone.

    If you use any other format, you'll get implementation-specific behavior, which often means parsing the date in the local time zone rather than UTC.

    This is why "2023-12-16" resolves to a Date represented as 2023-12-16T00:00:00.000Z. It is midnight of Dec 16th in the UTC time zone.

    However, "2023-1-1" is not a Date Time String Format, so it is parsed using an implementation-specific format, which includes using the local time zone. This is why the result is a Date represented as 2023-01-01T07:00:00.000Z... it's midnight of Dec 16th in your local time zone, which is 7 hours behind UTC (UTC-7), which probably means you are generating this Date in the North American Mountain Time Zone.

    The string "2023-9-14" is likewise parsed in the local time zone, but since it's during the summer and the area you are in appears to use Daylight Savings, a summer date like this is only 6 hours behind UTC (UTC-6), so the result is 2023-09-14T06:00:00.000Z

    1 There are caveats, such that Date.parse() must always result in the same Date generated by Date.prototype.toString() (and the value generated by toString() has its own standard which is quite lengthy), but it is sufficient to say that if you want to use a Date string that will be consistently interpreted by all ECMA-262 conforming Javascript implementations (which is pretty much all of them), then you should pad your month and day with zeros: "2023-01-01"