Wrong time format imported from google sheets #676

New Issue

2017-06-08T07:57:11Z

vvaldersteins commented

2017-06-08 07:57:11 +00:00

(Migrated from github.com)

Hi,

There is an issue when trying to export google sheets document, which has time format

h":"mm" "am/pm

So for example -> 12:00 AM

It will automatically change AM PM to a1/p1 or a2/p2, a3/p3 or a4/p4 .

Not sure if it's related to the google sheets or the library.

If time is entered as a plain text - without the time format, then it works as expected, but, as soon as, time format is applied it gives back this strange a1/p1, e.t.c. instead of AM or PM.

Hi, There is an issue when trying to export google sheets document, which has time format `h":"mm" "am/pm` So for example -> 12:00 AM It will automatically change AM PM to a1/p1 or a2/p2, a3/p3 or a4/p4 . Not sure if it's related to the google sheets or the library. If time is entered as a plain text - without the time format, then it works as expected, but, as soon as, time format is applied it gives back this strange a1/p1, e.t.c. instead of AM or PM.

SheetJSDev commented

2017-06-08 08:27:04 +00:00

(Migrated from github.com)

First, thanks for reporting :) This is ultimately a bug in google sheets but we should try to replicate Excel's repair process.

What is the correct interpretation of the format?
The number format grammar is laid out in the description of the XLS Format Record. The relevant comment regards the interpretation of AM/PM:

INTL-AMPM = (ASCII-CAPITAL-LETTER-A ASCII-CAPITAL-LETTER-M ASCII-SOLIDUS ASCII-CAPITAL-LETTER-P ASCII-CAPITAL-LETTER-M) / "A/P"

The key point is that the correct specification is AM/PM or A/P -- a/p and am/pm are not to be interpreted as the midday marker.

How is Excel magically fixing this?

My best guess is that Excel automagically converts am/pm to AM/PM. I checked a few older versions of Excel and they make the same corrections.

Why is SheetJS giving a different result?

The SSF Formatter is designed to largely conform to the specs. Based on the grammar, the letter "a" is to be interpreted as a bare letter a, "p" is to be interpreted as a bare letter p, and m is to be interpreted as the month. If you take 12:00 AM, the default assumed date is January 1 1900, hence 1 for the m. You can play around with the SSF Demo to see the behavior in action.

To prove that this is largely consistent with Excel, change the number format to h:mm am/p (no trailing "m") and you will see the same a1/p.

Fix
That is easily fixed -- since neither a nor p are significant on their own, the A case can be modified to include a. If that's of interest to you, we'd accept a PR in the dependency and then rebuild this library.

First, thanks for reporting :) This is ultimately a bug in google sheets but we should try to replicate Excel's repair process. **What is the correct interpretation of the format?** The number format grammar is laid out in the description of the [XLS Format Record](https://msdn.microsoft.com/en-us/library/dd944946.aspx). The relevant comment regards the interpretation of AM/PM: > INTL-AMPM = (ASCII-CAPITAL-LETTER-A ASCII-CAPITAL-LETTER-M ASCII-SOLIDUS ASCII-CAPITAL-LETTER-P ASCII-CAPITAL-LETTER-M) / "A/P" The key point is that the correct specification is `AM/PM` or `A/P` -- `a/p` and `am/pm` are not to be interpreted as the midday marker. **How is Excel magically fixing this?** My best guess is that Excel automagically converts `am/pm` to `AM/PM`. I checked a few older versions of Excel and they make the same corrections. **Why is SheetJS giving a different result?** The [SSF Formatter](https://github.com/sheetjs/ssf) is designed to largely conform to the specs. Based on the grammar, the letter "a" is to be interpreted as a bare letter `a`, "p" is to be interpreted as a bare letter `p`, and `m` is to be interpreted as the month. If you take 12:00 AM, the default assumed date is January 1 1900, hence `1` for the `m`. You can play around with the [SSF Demo](http://oss.sheetjs.com/ssf/) to see the behavior in action. To prove that this is largely consistent with Excel, change the number format to `h:mm am/p` (no trailing "m") and you will see the same `a1/p`. **Fix** That is easily fixed -- since neither `a` nor `p` are significant on their own, [the `A` case can be modified to include `a`](https://github.com/SheetJS/ssf/blob/master/bits/82_eval.js#L34). If that's of interest to you, we'd accept a PR in the dependency and then rebuild this library.

Sign in to join this conversation.