hh:mm:ss formatting bug #2560

New Issue

2018-01-09T23:24:42Z

phillipperalez commented

2018-01-09 23:24:42 +00:00

(Migrated from github.com)

when time is of a value that should round up the minute, if the second has a u (rounding value I assume) greater than or equal to .5, then the seconds will go to 00 without bumping the minute value up one. In excel the time is properly rounded

In this example I am using require('make-runnable') at the end of ssf.js to test this via CLI. The value is from 119.99 / 86400 - so the result I expect should be 00:02:00, however I receive 00:01:00 instead

node ssf.js format hh:mm:ss 0.001388773

when time is of a value that should round up the minute, if the second has a u (rounding value I assume) greater than or equal to .5, then the seconds will go to `00` without bumping the minute value up one. In excel the time is properly rounded In this example I am using ` require('make-runnable') ` at the end of ` ssf.js ` to test this via CLI. The value is from 119.99 / 86400 - so the result I expect should be 00:02:00, however I receive 00:01:00 instead ` node ssf.js format hh:mm:ss 0.001388773 `

phillipperalez commented

2018-01-09 23:30:48 +00:00

(Migrated from github.com)

71f827c4fa/ssf.js (L664-L693)

The only logic I am having trouble with is why the hour or minute values are only given a chance to round up in the case that a lesser time increment does not exist. for example if the format is hh:mm bt would equal 2 and minutes would have a chance to round up

https://github.com/SheetJS/ssf/blob/71f827c4faa89475996acb2458161bac2bbd4280/ssf.js#L664-L693 The only logic I am having trouble with is why the hour or minute values are only given a chance to round up in the case that a lesser time increment does not exist. for example if the format is ` hh:mm` bt would equal 2 and minutes would have a chance to round up

SheetJSDev commented

2018-01-11 18:40:24 +00:00

(Migrated from github.com)

@phillipperalez There's a grammar for the format string but the specifications don't actually explain how Excel converts a value to a specific type. Excel's time behavior behavior, at least based on when we last looked into it with Excel 2010/2011, is dependent on the specifiers in the format (so rounding when the finest time unit is hh is different from when the finest time unit is mm, which is different from the ss and the .000 case). Since the actual algorithms seem to change between versions, we will go back and normalize for Excel 2016.

P.S.: NodeJS module ships with an ssf script so you can run directly if you install globally:

$ npm install -g ssf
$ ssf "hh:mm:ss" 0.001388773
0.001388773|00:01:00|

and you can also test in the browser at http://oss.sheetjs.com/ssf/

@phillipperalez There's a grammar for the format string but the specifications don't actually explain how Excel converts a value to a specific type. Excel's time behavior behavior, at least based on when we last looked into it with Excel 2010/2011, is dependent on the specifiers in the format (so rounding when the finest time unit is `hh` is different from when the finest time unit is `mm`, which is different from the `ss` and the `.000` case). Since the actual algorithms seem to change between versions, we will go back and normalize for Excel 2016. P.S.: NodeJS module ships with an `ssf` script so you can run directly if you install globally: ```bash $ npm install -g ssf $ ssf "hh:mm:ss" 0.001388773 0.001388773|00:01:00| ``` and you can also test in the browser at http://oss.sheetjs.com/ssf/

snoopyjc commented

2020-09-21 14:35:45 +00:00

(Migrated from github.com)

The rounding algorithm needs to be rethought. Basically if you round the uS, then you need to carry that rounding all the way across, so it's better to just punt and call parse_date_code again with the adjusted time. You're also double-rounding in the [ss].000 case. Here are some failing test cases:

mm/dd/yyyy hh:mm:ss.000, 4018.99999998843 = 12/31/1910 23:59:59.999 (correct)
mm/dd/yyyy hh:mm:ss.00, 4018.99999998843 = 12/31/1910 23:59:00.00 (should be 01/01/1911 00:00:00.00)
mm/dd/yyyy hh:mm, 4018.99999998843 = 12/31/1910 24:00 (should be 01/01/1911 00:00)
[hh]:mm:ss.000, 4018.99999998843 = 96455:59:59.999 (correct)
[hh]:mm:ss.00, 4018.99999998843 = 96455:59:00.00 (should be 96456:00:00.00)
[hh]:mm:ss, 4018.99999998843 = 96455:59:00 (should be 96456:00:00)
[hh]:mm, 4018.99999998843 = 96456:00 (correct)
[hh], 4018.99999998843 = 96456 (correct)
[mm]:ss.000, 4018.99999998843 = 5787359:59.999 (correct)
[mm]:ss.00, 4018.99999998843 = 5787359:00.00 (should be 5787360:00.00)
[mm]:ss, 4018.99999998843 = 5787359:00 (should be 5787360:00)
[ss].000, 4018.99999998843 = 347241600.999 (should be 347241599.999)
[ss].00, 4018.99999998843 = 347241600.00 (correct)
[ss], 4018.99999998843 = 347241600 (correct)

The rounding algorithm needs to be rethought. Basically if you round the uS, then you need to carry that rounding all the way across, so it's better to just punt and call parse_date_code again with the adjusted time. You're also double-rounding in the [ss].000 case. Here are some failing test cases: mm/dd/yyyy hh:mm:ss.000, 4018.99999998843 = 12/31/1910 23:59:59.999 (correct) mm/dd/yyyy hh:mm:ss.00, 4018.99999998843 = 12/31/1910 23:59:00.00 (should be 01/01/1911 00:00:00.00) mm/dd/yyyy hh:mm, 4018.99999998843 = 12/31/1910 24:00 (should be 01/01/1911 00:00) [hh]:mm:ss.000, 4018.99999998843 = 96455:59:59.999 (correct) [hh]:mm:ss.00, 4018.99999998843 = 96455:59:00.00 (should be 96456:00:00.00) [hh]:mm:ss, 4018.99999998843 = 96455:59:00 (should be 96456:00:00) [hh]:mm, 4018.99999998843 = 96456:00 (correct) [hh], 4018.99999998843 = 96456 (correct) [mm]:ss.000, 4018.99999998843 = 5787359:59.999 (correct) [mm]:ss.00, 4018.99999998843 = 5787359:00.00 (should be 5787360:00.00) [mm]:ss, 4018.99999998843 = 5787359:00 (should be 5787360:00) [ss].000, 4018.99999998843 = 347241600.999 (should be 347241599.999) [ss].00, 4018.99999998843 = 347241600.00 (correct) [ss], 4018.99999998843 = 347241600 (correct)

Sign in to join this conversation.