Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project!

## #934 Serial Dates Are Being Calculated Incorrectly

1.0.x
closed-works-for-me
David Gilbert
5
2009-05-22
2009-05-21
bob messenger
No

I think I've found a pretty major bug in the 'private int calcSerial(final int d, final int m, final int y)' function of the SpreadsheetDate class in the jcommon library.

if (m > MonthConstants.FEBRUARY) {

should be replaced with either:

if (m >= MonthConstants.FEBRUARY) {

or

if (m > 1) {

To avoid dependency, the arguments specifically have to be zero based months. Maybe something like this:

public static int calcSerial(final int d, final int m, final int y)
{
final int yy = ((y - 1900) * 365) + SerialDate.leapYearCount(y - 1);
int mm = AGGREGATE_DAYS_TO_END_OF_PRECEDING_MONTH[m];
if (m > 1 && SerialDate.isLeapYear(y))
return yy + mm + d + 2;
return yy + mm + d + 1;
}

Cheers, Bob

## Discussion

• bob messenger
2009-05-21

• priority: 5 --> 9

• David Gilbert
2009-05-22

I think the code is correct, but if you think otherwise then please provide an example where the method returns the wrong serial number for a date (it is intended that this class should return the same integer value that Excel stores for dates).

The code is trying to account for the leap day in February, if the current year is a leap year. We don't need to count this in 'mm' unless the date is after Feb 29, so we just test that the month is March or later - that is, m > MonthConstants.FEBRUARY.

I'll close the bug report - feel free to reopen it if you can supply a failing test case.

Best regards,

Dave Gilbert