Details

    • Type: Bug
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: 2.5.0, 3.0.0 RC
    • Fix Version/s: 3.0.0 RC
    • Component/s: ZSS Component
    • Labels:

      Description

      evaluation result of HOUR, MINUTE, SECOND are #VALUE!.

      java.lang.AssertionError: =SECOND("4:48 PM") failed, at row 23 in xsheet: formula-datetime
      Expected: "0"
           but: was "#VALUE!"
      	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
      	at org.junit.Assert.assertThat(Assert.java:865)
      	at org.junit.rules.ErrorCollector$1.call(ErrorCollector.java:65)
      	at org.junit.rules.ErrorCollector.checkSucceeds(ErrorCollector.java:78)
      	at org.junit.rules.ErrorCollector.checkThat(ErrorCollector.java:63)
      	at zss.test.FormulaTest.testFormulaByRangesInSheet(FormulaTest.java:158)
      	at zss.test.FormulaTest.testDateTime(FormulaTest.java:87)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	
      

      evaluation result of WORKDAY is not as expected.

      java.lang.AssertionError: =WORKDAY(DATE(2013,4,1),5) failed, at row 33 in xsheet: formula-datetime
      Expected: "41372"
           but: was "41366"
      	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
      	at org.junit.Assert.assertThat(Assert.java:865)
      	at org.junit.rules.ErrorCollector$1.call(ErrorCollector.java:65)
      	at org.junit.rules.ErrorCollector.checkSucceeds(ErrorCollector.java:78)
      	at org.junit.rules.ErrorCollector.checkThat(ErrorCollector.java:63)
      	at zss.test.FormulaTest.testFormulaByRangesInSheet(FormulaTest.java:158)
      	at zss.test.FormulaTest.testDateTime(FormulaTest.java:87)
      	
      
      

      not found formula: TIMEVALUE"

      
      java.lang.AssertionError: =TIMEVALUE("2:24 AM") failed, at row 27 in xsheet: formula-datetime
      Expected: "0.1"
           but: was "#NAME?"
      	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
      	at org.junit.Assert.assertThat(Assert.java:865)
      	at org.junit.rules.ErrorCollector$1.call(ErrorCollector.java:65)
      	at org.junit.rules.ErrorCollector.checkSucceeds(ErrorCollector.java:78)
      	at org.junit.rules.ErrorCollector.checkThat(ErrorCollector.java:63)
      	at zss.test.FormulaTest.testFormulaByRangesInSheet(FormulaTest.java:158)
      	at zss.test.FormulaTest.testDateTime(FormulaTest.java:87)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      
      
      

      reproducing steps

      run automatic test case FormulaTest of the project zss.test of zkspreadsheet or attached file

        Activity

        Hide
        hawk hawk added a comment -

        Root Cause:

        HOUR, MINUTE, SECOND only accepts a cell reference which points to a cell with date string instead of a date string.

        WORKDAY's implementation contains calculation error. in pastDaysOfWeek(), it doesn't consider the case that start equals end.

        Show
        hawk hawk added a comment - Root Cause: HOUR, MINUTE, SECOND only accepts a cell reference which points to a cell with date string instead of a date string. WORKDAY 's implementation contains calculation error. in pastDaysOfWeek() , it doesn't consider the case that start equals end.
        Hide
        hawk hawk added a comment -

        for NETWORKDAYS, WORKDAY

        Root Cause:

        WORKDAY's implementation contains calculation error. in pastDaysOfWeek(), if start equals end, it returns negative number but it should be positive.
        This method calculate how many day of week (e.g. Sunday) between start to end.
        for example:
        start=2013/6/16(Sun), end=2013/6/16(Sun)
        It should return 1 but original implementation return -1;

        NETWORKDAYS also calculate incorrectly when the given duration are all holidays, e.g. =NETWORKDAYS(DATE(2013,6,1), DATE(2013,6,1))

        Solution:

        WORKDAY:
        Make original algorithm more readable.

        //calculate 1st target day by adding workday to start day
        while (true){
            //count holidays between start day and target day
        	//if no holidays, stop
        	//else
        	//    move the target day by adding holidays
        }
        

        NETWORKDAYS:
        Always return a positive day count when counting Staturday, Sunday, and specified holidays. Because original caller method doesn't expect pastDaysOfWeek() and calculateNonWeekendHolidays() return negative value.

        Show
        hawk hawk added a comment - for NETWORKDAYS, WORKDAY Root Cause: WORKDAY's implementation contains calculation error. in pastDaysOfWeek(), if start equals end, it returns negative number but it should be positive. This method calculate how many day of week (e.g. Sunday) between start to end. for example: start=2013/6/16(Sun), end=2013/6/16(Sun) It should return 1 but original implementation return -1; NETWORKDAYS also calculate incorrectly when the given duration are all holidays, e.g. =NETWORKDAYS(DATE(2013,6,1), DATE(2013,6,1)) Solution: WORKDAY: Make original algorithm more readable. //calculate 1st target day by adding workday to start day while ( true ){ //count holidays between start day and target day // if no holidays, stop // else // move the target day by adding holidays } NETWORKDAYS: Always return a positive day count when counting Staturday, Sunday, and specified holidays. Because original caller method doesn't expect pastDaysOfWeek() and calculateNonWeekendHolidays() return negative value.

          People

          • Assignee:
            hawk hawk
            Reporter:
            hawk hawk
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: