ASF Bugzilla – Attachment 30920 Details for
Bug 55649
ATP - WORKDAY Function returns incorrect date
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
Patch with Unit Tests and Fix
WORKDAY.patch (text/plain), 4.74 KB, created by
stuart
on 2013-10-10 20:57:38 UTC
(
hide
)
Description:
Patch with Unit Tests and Fix
Filename:
MIME Type:
Creator:
stuart
Created:
2013-10-10 20:57:38 UTC
Size:
4.74 KB
patch
obsolete
>Index: src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java >=================================================================== >--- src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java (revision 1530139) >+++ src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java (working copy) >@@ -61,24 +61,24 @@ > * @param holidays an array of holidays. > * @return date past x workdays. > */ >- public Date calculateWorkdays(double start, int workdays, double[] holidays) { >- Date startDate = DateUtil.getJavaDate(start); >- Calendar endDate = Calendar.getInstance(); >- endDate.setTime(startDate); >- endDate.add(Calendar.DAY_OF_YEAR, workdays); >- int skippedDays = 0; >- do { >- double end = DateUtil.getExcelDate(endDate.getTime()); >- int saturdaysPast = this.pastDaysOfWeek(start, end, Calendar.SATURDAY); >- int sundaysPast = this.pastDaysOfWeek(start, end, Calendar.SUNDAY); >- int nonWeekendHolidays = this.calculateNonWeekendHolidays(start, end, holidays); >- skippedDays = saturdaysPast + sundaysPast + nonWeekendHolidays; >- endDate.add(Calendar.DAY_OF_YEAR, skippedDays); >- start = end + isNonWorkday(end, holidays); >- } while (skippedDays != 0); >- return endDate.getTime(); >- } >- >+ public Date calculateWorkdays(double start, int workdays, double[] holidays) { >+ Date startDate = DateUtil.getJavaDate(start); >+ int direction = workdays < 0 ? -1 : 1; >+ Calendar endDate = Calendar.getInstance(); >+ endDate.setTime(startDate); >+ double excelEndDate = DateUtil.getExcelDate(endDate.getTime()); >+ while (workdays != 0) { >+ endDate.add(Calendar.DAY_OF_YEAR, direction); >+ excelEndDate += direction; >+ if (endDate.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY >+ && endDate.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY >+ && !isHoliday(excelEndDate, holidays)) { >+ workdays -= direction; >+ } >+ } >+ return endDate.getTime(); >+ } >+ > /** > * Calculates how many days of week past between a start and an end date. > * >Index: src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java >=================================================================== >--- src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java (revision 1530139) >+++ src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java (working copy) >@@ -85,6 +85,46 @@ > new StringEval(STARTING_DATE.toString()), new NumberEval(151) }, EC)).getNumberValue())); > } > >+ public void testReturnWorkdaysSpanningAWeekendSubtractingDays() { >+ String startDate = "2013/09/30"; >+ int days = -1; >+ String expectedWorkDay = "2013/09/27"; >+ StringEval stringEval = new StringEval(startDate); >+ double numberValue = ((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{ >+ stringEval, new NumberEval(days) }, EC)).getNumberValue(); >+ assertEquals(expectedWorkDay, formatter.format(DateUtil.getJavaDate(numberValue))); >+ } >+ >+ public void testReturnWorkdaysSpanningAWeekendAddingDays() { >+ String startDate = "2013/09/27"; >+ int days = 1; >+ String expectedWorkDay = "2013/09/30"; >+ StringEval stringEval = new StringEval(startDate); >+ double numberValue = ((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{ >+ stringEval, new NumberEval(days) }, EC)).getNumberValue(); >+ assertEquals(expectedWorkDay, formatter.format(DateUtil.getJavaDate(numberValue))); >+ } >+ >+ public void testReturnWorkdaysWhenStartIsWeekendAddingDays() { >+ String startDate = "2013/10/06"; >+ int days = 1; >+ String expectedWorkDay = "2013/10/07"; >+ StringEval stringEval = new StringEval(startDate); >+ double numberValue = ((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{ >+ stringEval, new NumberEval(days) }, EC)).getNumberValue(); >+ assertEquals(expectedWorkDay, formatter.format(DateUtil.getJavaDate(numberValue))); >+ } >+ >+ public void testReturnWorkdaysWhenStartIsWeekendSubtractingDays() { >+ String startDate = "2013/10/06"; >+ int days = -1; >+ String expectedWorkDay = "2013/10/04"; >+ StringEval stringEval = new StringEval(startDate); >+ double numberValue = ((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{ >+ stringEval, new NumberEval(days) }, EC)).getNumberValue(); >+ assertEquals(expectedWorkDay, formatter.format(DateUtil.getJavaDate(numberValue))); >+ } >+ > public void testReturnWorkdaysWithDaysTruncated() { > assertEquals(new Date(109, APRIL, 30), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{ > new StringEval(STARTING_DATE.toString()), new NumberEval(151.99999) }, EC)).getNumberValue()));
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Raw
Actions:
View
Attachments on
bug 55649
: 30920