sheetjs
sheetjs copied to clipboard
SheetJS
Date format 14 should have yyyy to correspond with modern Excel
The default format for dates in Excel 2010 and newer includes a 4-digit year. So SSF.format(14, new Date(2017, 1, 19)) (or using 'General' format) should be "2/19/2017", not "2/19/17".
Here is documentation on this. Note there are other related issues mentioned here too: https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/0e59abdb-7f4e-48fc-9b89-67832fa11789
Addl Changes:
t[22]= 'm/d/yyyy h:mm'
t[37]= '#,##0_);(#,##0)'
t[38]= '#,##0_);[Red](#,##0)'
t[39]= '#,##0.00_);(#,##0.00)'
t[40]= '#,##0.00_);[Red](#,##0.00)'
t[47]= 'mm:ss.0'
t[55]: "yyyy/mm/dd" //ko
Here is an updated implied.json. Note that I use dateNF="m/d/yy" so I didn't change those entries:
[ [1234567890000, [ [0, "1.23457E+12"], [1, "1234567890000"], [2, "1234567890000.00"], [3, "1,234,567,890,000"], [4, "1,234,567,890,000.00"], [9, "123456789000000%"], [10, "123456789000000.00%"], [11, "1.23E+12"], [12, "1234567890000 "], [13, "1234567890000 "], [14, "##########"], [15, "##########"], [16, "##########"], [17, "##########"], [18, "##########"], [19, "##########"], [20, "##########"], [21, "##########"], [22, "##########"], [37, "1,234,567,890,000 "], [38, "1,234,567,890,000 "], [39, "1,234,567,890,000.00 "], [40, "1,234,567,890,000.00 "], [45, "##########"], [46, "##########"], [47, "##########"], [48, "1.2E+12"], [49, "1.23457E+12"] ]],
[123456789000, [ [0, "1.23457E+11"], [1, "123456789000"], [2, "123456789000.00"], [3, "123,456,789,000"], [4, "123,456,789,000.00"], [9, "12345678900000%"], [10, "12345678900000.00%"], [11, "1.23E+11"], [12, "123456789000 "], [13, "123456789000 "], [14, "##########"], [15, "##########"], [16, "##########"], [17, "##########"], [18, "##########"], [19, "##########"], [20, "##########"], [21, "##########"], [22, "##########"], [37, "123,456,789,000 "], [38, "123,456,789,000 "], [39, "123,456,789,000.00 "], [40, "123,456,789,000.00 "], [45, "##########"], [46, "##########"], [47, "##########"], [48, "123.5E+9"], [49, "1.23457E+11"] ]],
[12345678900, [ [0, "12345678900"], [1, "12345678900"], [2, "12345678900.00"], [3, "12,345,678,900"], [4, "12,345,678,900.00"], [9, "1234567890000%"], [10, "1234567890000.00%"], [11, "1.23E+10"], [12, "12345678900 "], [13, "12345678900 "], [14, "##########"], [15, "##########"], [16, "##########"], [17, "##########"], [18, "##########"], [19, "##########"], [20, "##########"], [21, "##########"], [22, "##########"], [37, "12,345,678,900 "], [38, "12,345,678,900 "], [39, "12,345,678,900.00 "], [40, "12,345,678,900.00 "], [45, "##########"], [46, "##########"], [47, "##########"], [48, "12.3E+9"], [49, "12345678900"] ]],
[1234567890, [ [0, "1234567890"], [1, "1234567890"], [2, "1234567890.00"], [3, "1,234,567,890"], [4, "1,234,567,890.00"], [9, "123456789000%"], [10, "123456789000.00%"], [11, "1.23E+09"], [12, "1234567890 "], [13, "1234567890 "], [14, "##########"], [15, "##########"], [16, "##########"], [17, "##########"], [18, "##########"], [19, "##########"], [20, "##########"], [21, "##########"], [22, "##########"], [37, "1,234,567,890 "], [38, "1,234,567,890 "], [39, "1,234,567,890.00 "], [40, "1,234,567,890.00 "], [45, "##########"], [46, "##########"], [47, "##########"], [48, "1.2E+9"], [49, "1234567890"] ]],
[123456789, [ [0, "123456789"], [1, "123456789"], [2, "123456789.00"], [3, "123,456,789"], [4, "123,456,789.00"], [9, "12345678900%"], [10, "12345678900.00%"], [11, "1.23E+08"], [12, "123456789 "], [13, "123456789 "], [14, "##########"], [15, "##########"], [16, "##########"], [17, "##########"], [18, "##########"], [19, "##########"], [20, "##########"], [21, "##########"], [22, "##########"], [37, "123,456,789 "], [38, "123,456,789 "], [39, "123,456,789.00 "], [40, "123,456,789.00 "], [45, "##########"], [46, "##########"], [47, "##########"], [48, "123.5E+6"], [49, "123456789"] ]],
[12345678.9, [ [0, "12345678.9"], [1, "12345679"], [2, "12345678.90"], [3, "12,345,679"], [4, "12,345,678.90"], [9, "1234567890%"], [10, "1234567890.00%"], [11, "1.23E+07"], [12, "12345679 "], [13, "12345678 9/10"], [14, "##########"], [15, "##########"], [16, "##########"], [17, "##########"], [18, "##########"], [19, "##########"], [20, "##########"], [21, "##########"], [22, "##########"], [37, "12,345,679 "], [38, "12,345,679 "], [39, "12,345,678.90 "], [40, "12,345,678.90 "], [45, "##########"], [46, "##########"], [47, "##########"], [48, "12.3E+6"], [49, "12345678.9"] ]],
[1234567.89, [ [0, "1234567.89"], [1, "1234568"], [2, "1234567.89"], [3, "1,234,568"], [4, "1,234,567.89"], [9, "123456789%"], [10, "123456789.00%"], [11, "1.23E+06"], [12, "1234567 8/9"], [13, "1234567 8/9 "], [14, "2/15/80"], [15, "15-Feb-80"], [16, "15-Feb"], [17, "Feb-80"], [18, "9:21 PM"], [19, "9:21:36 PM"], [20, "21:21"], [21, "21:21:36"], [22, "2/15/5280 21:21"], [37, "1,234,568 "], [38, "1,234,568 "], [39, "1,234,567.89 "], [40, "1,234,567.89 "], [45, "21:36"], [46, "29629629:21:36"], [47, "21:36.0"], [48, "1.2E+6"], [49, "1234567.89"] ]],
[123456.789, [ [0, "123456.789"], [1, "123457"], [2, "123456.79"], [3, "123,457"], [4, "123,456.79"], [9, "12345679%"], [10, "12345678.90%"], [11, "1.23E+05"], [12, "123456 4/5"], [13, "123456 15/19"], [14, "1/3/38"], [15, "3-Jan-38"], [16, "3-Jan"], [17, "Jan-38"], [18, "6:56 PM"], [19, "6:56:10 PM"], [20, "18:56"], [21, "18:56:10"], [22, "1/3/2238 18:56"], [37, "123,457 "], [38, "123,457 "], [39, "123,456.79 "], [40, "123,456.79 "], [45, "56:10"], [46, "2962962:56:10"], [47, "56:09.6"], [48, "123.5E+3"], [49, "123456.789"] ]],
[12345.6789, [ [0, "12345.6789"], [1, "12346"], [2, "12345.68"], [3, "12,346"], [4, "12,345.68"], [9, "1234568%"], [10, "1234567.89%"], [11, "1.23E+04"], [12, "12345 2/3"], [13, "12345 55/81"], [14, "10/18/33"], [15, "18-Oct-33"], [16, "18-Oct"], [17, "Oct-33"], [18, "4:17 PM"], [19, "4:17:37 PM"], [20, "16:17"], [21, "16:17:37"], [22, "10/18/1933 16:17"], [37, "12,346 "], [38, "12,346 "], [39, "12,345.68 "], [40, "12,345.68 "], [45, "17:37"], [46, "296296:17:37"], [47, "17:37.0"], [48, "12.3E+3"], [49, "12345.6789"] ]],
[1234.56789, [ [0, "1234.56789"], [1, "1235"], [2, "1234.57"], [3, "1,235"], [4, "1,234.57"], [9, "123457%"], [10, "123456.79%"], [11, "1.23E+03"], [12, "1234 4/7"], [13, "1234 46/81"], [14, "5/18/03"], [15, "18-May-03"], [16, "18-May"], [17, "May-03"], [18, "1:37 PM"], [19, "1:37:46 PM"], [20, "13:37"], [21, "13:37:46"], [22, "5/18/1903 13:37"], [37, "1,235 "], [38, "1,235 "], [39, "1,234.57 "], [40, "1,234.57 "], [45, "37:46"], [46, "29629:37:46"], [47, "37:45.7"], [48, "1.2E+3"], [49, "1234.56789"] ]],
[123.456789, [ [0, "123.456789"], [1, "123"], [2, "123.46"], [3, "123"], [4, "123.46"], [9, "12346%"], [10, "12345.68%"], [11, "1.23E+02"], [12, "123 1/2"], [13, "123 37/81"], [14, "5/2/00"], [15, "2-May-00"], [16, "2-May"], [17, "May-00"], [18, "10:57 AM"], [19, "10:57:47 AM"], [20, "10:57"], [21, "10:57:47"], [22, "5/2/1900 10:57"], [37, "123 "], [38, "123 "], [39, "123.46 "], [40, "123.46 "], [45, "57:47"], [46, "2962:57:47"], [47, "57:46.6"], [48, "123.5E+0"], [49, "123.456789"] ]],
[12.3456789, [ [0, "12.3456789"], [1, "12"], [2, "12.35"], [3, "12"], [4, "12.35"], [9, "1235%"], [10, "1234.57%"], [11, "1.23E+01"], [12, "12 1/3"], [13, "12 28/81"], [14, "1/12/00"], [15, "12-Jan-00"], [16, "12-Jan"], [17, "Jan-00"], [18, "8:17 AM"], [19, "8:17:47 AM"], [20, "8:17"], [21, "8:17:47"], [22, "1/12/1900 8:17"], [37, "12 "], [38, "12 "], [39, "12.35 "], [40, "12.35 "], [45, "17:47"], [46, "296:17:47"], [47, "17:46.7"], [48, "12.3E+0"], [49, "12.3456789"] ]],
[1.23456789, [ [0, "1.23456789"], [1, "1"], [2, "1.23"], [3, "1"], [4, "1.23"], [9, "123%"], [10, "123.46%"], [11, "1.23E+00"], [12, "1 1/4"], [13, "1 19/81"], [14, "1/1/00"], [15, "1-Jan-00"], [16, "1-Jan"], [17, "Jan-00"], [18, "5:37 AM"], [19, "5:37:47 AM"], [20, "5:37"], [21, "5:37:47"], [22, "1/1/1900 5:37"], [37, "1 "], [38, "1 "], [39, "1.23 "], [40, "1.23 "], [45, "37:47"], [46, "29:37:47"], [47, "37:46.7"], [48, "1.2E+0"], [49, "1.23456789"] ]],
[0.123456789, [ [0, "0.123456789"], [1, "0"], [2, "0.12"], [3, "0"], [4, "0.12"], [9, "12%"], [10, "12.35%"], [11, "1.23E-01"], [12, " 1/8"], [13, " 10/81"], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "2:57 AM"], [19, "2:57:47 AM"], [20, "2:57"], [21, "2:57:47"], [22, "1/0/1900 2:57"], [37, "0 "], [38, "0 "], [39, "0.12 "], [40, "0.12 "], [45, "57:47"], [46, "2:57:47"], [47, "57:46.7"], [48, "123.5E-3"], [49, "0.123456789"] ]],
[0.0123456789, [ [0, "0.012345679"], [1, "0"], [2, "0.01"], [3, "0"], [4, "0.01"], [9, "1%"], [10, "1.23%"], [11, "1.23E-02"], [12, "0 "], [13, " 1/81"], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:17 AM"], [19, "12:17:47 AM"], [20, "0:17"], [21, "0:17:47"], [22, "1/0/1900 0:17"], [37, "0 "], [38, "0 "], [39, "0.01 "], [40, "0.01 "], [45, "17:47"], [46, "0:17:47"], [47, "17:46.7"], [48, "12.3E-3"], [49, "0.012345679"] ]],
[0.00123456789, [ [0, "0.001234568"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.12%"], [11, "1.23E-03"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:01 AM"], [19, "12:01:47 AM"], [20, "0:01"], [21, "0:01:47"], [22, "1/0/1900 0:01"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "01:47"], [46, "0:01:47"], [47, "01:46.7"], [48, "1.2E-3"], [49, "0.001234568"] ]],
[0.000123456789, [ [0, "0.000123457"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.01%"], [11, "1.23E-04"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:00 AM"], [19, "12:00:11 AM"], [20, "0:00"], [21, "0:00:11"], [22, "1/0/1900 0:00"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "00:11"], [46, "0:00:11"], [47, "00:10.7"], [48, "123.5E-6"], [49, "0.000123457"] ]],
[0.0000123456789, [ [0, "1.23457E-05"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.00%"], [11, "1.23E-05"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:00 AM"], [19, "12:00:01 AM"], [20, "0:00"], [21, "0:00:01"], [22, "1/0/1900 0:00"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "00:01"], [46, "0:00:01"], [47, "00:01.1"], [48, "12.3E-6"], [49, "1.23457E-05"] ]],
[0.00000123456789, [ [0, "1.23457E-06"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.00%"], [11, "1.23E-06"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:00 AM"], [19, "12:00:00 AM"], [20, "0:00"], [21, "0:00:00"], [22, "1/0/1900 0:00"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "00:00"], [46, "0:00:00"], [47, "00:00.1"], [48, "1.2E-6"], [49, "1.23457E-06"] ]],
[0.000000123456789, [ [0, "1.23457E-07"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.00%"], [11, "1.23E-07"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:00 AM"], [19, "12:00:00 AM"], [20, "0:00"], [21, "0:00:00"], [22, "1/0/1900 0:00"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "00:00"], [46, "0:00:00"], [47, "00:00.0"], [48, "123.5E-9"], [49, "1.23457E-07"] ]],
[0.0000000123456789, [ [0, "1.23457E-08"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.00%"], [11, "1.23E-08"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:00 AM"], [19, "12:00:00 AM"], [20, "0:00"], [21, "0:00:00"], [22, "1/0/1900 0:00"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "00:00"], [46, "0:00:00"], [47, "00:00.0"], [48, "12.3E-9"], [49, "1.23457E-08"] ]],
[0.00000000123456789, [ [0, "1.23457E-09"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.00%"], [11, "1.23E-09"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:00 AM"], [19, "12:00:00 AM"], [20, "0:00"], [21, "0:00:00"], [22, "1/0/1900 0:00"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "00:00"], [46, "0:00:00"], [47, "00:00.0"], [48, "1.2E-9"], [49, "1.23457E-09"] ]],
[0.000000000123456789, [ [0, "1.23457E-10"], [1, "0"], [2, "0.00"], [3, "0"], [4, "0.00"], [9, "0%"], [10, "0.00%"], [11, "1.23E-10"], [12, "0 "], [13, "0 "], [14, "1/0/00"], [15, "0-Jan-00"], [16, "0-Jan"], [17, "Jan-00"], [18, "12:00 AM"], [19, "12:00:00 AM"], [20, "0:00"], [21, "0:00:00"], [22, "1/0/1900 0:00"], [37, "0 "], [38, "0 "], [39, "0.00 "], [40, "0.00 "], [45, "00:00"], [46, "0:00:00"], [47, "00:00.0"], [48, "123.5E-12"], [49, "1.23457E-10"] ]],
[-12345.6789, [ [0, "-12345.6789"], [1, "-12346"], [2, "-12345.68"], [3, "-12,346"], [4, "-12,345.68"], [9, "-1234568%"], [10, "-1234567.89%"], [11, "-1.23E+04"], [12, "-12345 2/3"], [13, "-12345 55/81"], [14, "##########"], [15, "##########"], [16, "##########"], [17, "##########"], [18, "##########"], [19, "##########"], [20, "##########"], [21, "##########"], [22, "##########"], [37, "(12,346)"], [38, "(12,346)"], [39, "(12,345.68)"], [40, "(12,345.68)"], [45, "##########"], [46, "##########"], [47, "##########"], [48, "-12.3E+3"], [49, "-12345.6789"] ]],
[11.666666666666666, 0, "11.66666667"], [5.057996968497839, 0, "5.057996968"], [4.380353866983808, 0, "4.380353867"], [12.333333333333343, 0, "12.33333333"], [-0.000006211546860868111, 0, "-6.21155E-06"], [12345.67876, 47, "17:24.9"], [0, 0, "0"] ]
What are your computer regional settings?
On a mac set to New York, NY, USA, I generated the following file: fmt14.xlsx
A1=TODAY()
The format is using code 14:
