Date is Undefined, NaN on Safari / IOS / Firefox

Question

I have the following function that converts Excel Date to normal date but it shows as "Undefined, NaN" on IOS and Safari. My Code:

export const convertExcelDateToMMMDD = (excelDate) => {
      const SECONDS_IN_DAY = 86400;
      const EPOCH_OFFSET = 25569;
      const excelToUnixTimestamp = (excelDate - EPOCH_OFFSET) * SECONDS_IN_DAY;
      const unixTimestamp =
        excelToUnixTimestamp - new Date().getTimezoneOffset() * 60;
      if (isNaN(unixTimestamp)) return "Invalid date";
      const date = new Date(unixTimestamp * 1000);
      const monthNames = [
        "JAN",
        "FEB",
        "MAR",
        "APR",
        "MAY",
        "JUN",
        "JUL",
        "AUG",
        "SEP",
        "OCT",
        "NOV",
        "DEC",
      ];
      const month = monthNames[date.getMonth()];
      const day = date.getDate();
      return `${month} ${day}`;
};

SS:

Please see the [How to Ask](https://stackoverflow.com/help/how-to-ask) page. We don't know what data you're passing in, what "SS" means, or how you get a comma-separated output when you return a string. — Dave Newton, Mar 11 '23 at 03:18
What value are you passing as *excelDate*? What is the expected result? What is the actual result? Note that `new Date().getTimezoneOffset()` will return the current offset, which may be different to that of the date represented by *excelDate* due to historic or daylight saving changes. — RobG, Mar 11 '23 at 11:58
BTW, the code runs fine in Safari, it might be something to do with module export/import. — RobG, Mar 11 '23 at 13:01

RobG · Answer 1 · 2023-03-13T13:38:21.960

There isn't enough information in the OP to determine why the code fails in iOS and Safari. The following might help.

If you're dealing only in whole days and dates after 1 Mar 1900, you can convert an Excel serial date value into an ECMAScript date by adding the value as days to the effective Excel epoch of 30 Dec 1899.

This deals with the issue that Excel serial 1 is 1 Jan 1900 and that 1900 is treated as a leap year, so there are effectively 2 extra days. A consequence is that dates before 1 Mar 1900 will be incorrect.

/* Given an Excel date value, return an ECMAScript Date
 * for the same date.
 * @param {number} excelValue: Excel date value
 * @param {boolean} useUTC: if true, treat excelValue as UTC
 *                  otherwise treat excelValue as local
 * @returns {Date}
 */
function excelToDate(excelValue, useUTC = false) {
 return useUTC? 
   new Date(Date.UTC(1899, 11, 30 + excelValue))
   :
   new Date(1899, 11, 30 + excelValue);
}

// Quick formatter - return date as MMM DD
// If utc == true, treat as UTC
// else treat as local
function toMD(date, utc = false) {
  let opts = {month: 'short', day: '2-digit'};
  if (utc) opts.timeZone = 'UTC';
  return date.toLocaleString('en-us', opts).toUpperCase();
}

[25569, // 01 Jan 1970
 27463, // 10 Mar 1975
 40537, // 25 Dec 2010
 44301, // 15 Apr 2021
].forEach(v =>
  console.log(
    'Value: ' + v + '\n' +
    'Local: ' + toMD(excelToDate(v)) + '\n' +
    'UTC  : ' + toMD(excelToDate(v, true), true)
  )
);

This approach avoids conversion to milliseconds and the problematic use of getTimezoneOffset. Decimal days need a little more work, especially if prior to the excel epoch.

How the date is formatted is really a separate concern. The use of toLocaleString for precise formatting is not generally encouraged, however the formatting of en-us is unlikely to change. Ever.

Edit

A more correct version that handles decimal days and corrects the 1900 leap year issue is below. Excel dates prior to 1 Jan 1900 are another issue again.

function excelToDate(excelValue, useUTC = false) {
  // Split into whole and part days
  let wholeDays = parseInt(excelValue);
  let partDays = excelValue - wholeDays;
  // Allow for 1900 leap year issue
  wholeDays -= wholeDays >= 60? 1 : 0;
  // Create date, assuming fractional days are based on 24 hour day
  return useUTC? new Date(Date.UTC(0,0, wholeDays) + 8.64e7 * partDays) :
                 new Date(0,0, wholeDays, 0, 0, 0, 8.64e7 * partDays);
}

// Examples
[36952.00010416667, // 02 Mar 2001 00:00:09
 1,                 // 01 Jan 1900 00:00:00
 40654.3837962963,  // 21 Apr 2011 09:12:40
 40729,             // 05 Jul 2011 00:00:00
 43103,             // 03 Jan 2018
 ].forEach(v => console.log(
   `${v}\nLocal : ${excelToDate(v).toString()}` +
       `\nUTC   : ${excelToDate(v, true).toISOString()}`
 ));

A second part of the question seems to be how to parse timestamps in the format m/d. Simply split into parts and give to the Date constructor. I have not idea how you work out the year, I'll just assume it's the current year.

function parseMD(d) {
  let [month, day] = d.split(/\D/);
  let date = new Date(new Date().getFullYear(), month - 1, day);
  return (month - date.getMonth() == 1 && date.getDate() == day)?
    date : new Date(NaN);
}

// Examples
['3/7',   //  7 Mar
 '12/31', // 31 Dec
 '2/29',  // 29 Feb, invalid Date in non–leap years
].forEach(d => 
  console.log(`${d} => ${parseMD(d).toDateString()}`)
);

Note that all the above uses ECMAScript that is compatible back to ed 3 (1999, except for the options to toLocaleString which should be ignored). It has been tested and works on iOS devices.

Hi, Not Working! it returns "Invalid Date" Here is the data to try on: 36952.00010416667 — Kareem Gameel, Mar 11 '23 at 19:17
@KareemGameel—as stated in the answer, decimal days need more work. You need to split off the decimal days part, calculate the new date then set the time based on decimal days. The reason it needs to be split is that for negative values, whole days are subtracted but the decimal days part is added. — RobG, Mar 12 '23 at 22:39

score 0 · Answer 2 · answered Mar 11 '23 at 22:27

To Test the function try this date (in days): 36957.00010416667 or "3/7" as string

 /**
   * Browsers Treat Date() Constractor like a B**ch.
   * Non-IOS Devices Convert (M/D) to Days like this: 36957.00010416667 or "3/7"
   * IOS Devices Reads "3/7" like a normal date they do not converting it to numeric value like Non-IOS devices.
   * P.S: All IOS Browsers works the same incl. FireFox/Safari/Chrome
   * Also Firefox on linux/windows treak the Date() like B**ch so if you had this bug during development please contact me on github:
   * github.com/kareem-g or @kareem-g
   * and check my StackOverFlow Question for future Updates.
   * https://stackoverflow.com/q/75702523/15945876
   *
   * Time Spent Here: ~6 Hours Lol
   */

  /** ChatGPT Explanation if you're in love with JavaScript
     This code is a utility function for converting date values that might come in different formats into a standard format of month and day.

    The function excelToDateForNonIOSDevices is used to convert dates in the format of 36957.00010416667 to JavaScript Date objects. This format is produced by some non-iOS devices that convert the (month/day) format into a numeric value representing the number of days since December 30, 1899. However, iOS devices do not perform this conversion and instead treat the string value "3/7" as a date value. The function excelToDateForNonIOSDevices takes the Excel date value and converts it to the equivalent JavaScript Date object by adding the value of the date in milliseconds to a base date of December 30, 1899.

    The function convertDateToMMMDD takes a date value as input, which can be either a string in the format "month/day" or a numeric value as produced by excelToDateForNonIOSDevices. If the input is a string, the function first validates the input format and then converts it to a JavaScript Date object. If the input is a numeric value, the function directly converts it to a Date object using the excelToDateForNonIOSDevices function. The resulting Date object is then formatted to the MMM DD format using toLocaleString().

    The comments in the code provide some additional information about the behavior of different browsers and devices when working with Date objects, and offer some guidance on how to use the functions in different scenarios.
   */



 // This function converts an Excel date value to a JavaScript Date object for non-iOS devices
  function excelToDateForNonIOSDevices(excelValue, useUTC = false) {
    const baseDate = useUTC ? Date.UTC(1899, 11, 30) : new Date(1899, 11, 30);
    const dateValue = excelValue * 24 * 60 * 60 * 1000;
    return new Date(baseDate + dateValue);
  }

  // This function converts a date string or an Excel date value to a formatted date string (MMM DD)
  function convertDateToMMMDD(dateString) {
    let date;
    if (typeof dateString === "string") {
      const [monthStr, dayStr] = dateString.split("/");
      const month = parseInt(monthStr, 10);
      const day = parseInt(dayStr, 10);
      if (
        isNaN(month) ||
        isNaN(day) ||
        month < 1 ||
        month > 12 ||
        day < 1 ||
        day > 31
      ) {
        throw new Error(
          `Invalid month or day value in date string: ${dateString}`
        );
      }
      date = new Date(2000, month - 1, day); // arbitrary year (2000) is used here
    } else if (typeof dateString === "number") {
      // ** here we use Math.floor() to get rid of the numbers after the Dot "."
      // If dateString is a number, assume it is an Excel date value and convert it to a Date object
      date = excelToDateForNonIOSDevices(Math.floor(item[0]), true);
    } else {
      throw new Error(`Invalid date value: ${dateString}`);
    }
    // Format the date object to a string in the format of MMM DD (e.g. "Mar 7")
    const formattedDate = date.toLocaleString("default", {
      month: "short",
      day: "numeric",
    });
    return formattedDate;
  }

  /**
   * * * Usage * *
   ** For IOS Devices/Browsers: console.log(convertDateToMMMDD("3/7")); // Output: "Mar 7"
   ** For Non-IOS Devices/Browsers:  console.log(excelToDateForNonIOSDevices(Math.floor(36957.00010416667), true);); // Output: "Mar 7"
   */

Don't add additional information as an "answer", add it to the original post (OP). If it's a new question, then ask a new question. This code contains a number of poor coding practices and really should be reviewed on an appropriate site. There are many existing good answers regarding parsing and formatting of dates and conversion from Excel serial date value to ECMAScript Date object. — RobG, Mar 12 '23 at 22:34

Date is Undefined, NaN on Safari / IOS / Firefox

2 Answers2

Edit