1

I have an excel document with times listed as "02:30" ... however, when iterating through them using NPOI, the DateCellValue flips to "02:29:59" ... This first happens on 1/1/2019 (which correctly stores as "02:30") but then on 1/2/2019 it flips to "02:29:59" ... Does anyone know how to have it simply get the value of the cell without trying to do any voodoo to it? It's obviously taking into account perhaps a leap second, or something? It is, however, clear as day in Excel as "02:30", and at my breakpoint with:

[Model].DepartureDttm = row.GetCell(j).DateCellValue [1/2/2019 2:29:59 AM]
Aaron
  • 1,313
  • 16
  • 26
  • I've also tried with new DateTime(row.GetCell(j).DateCellValue.Ticks,DateTimeKind.Utc) same result.. 1 second off. – Aaron Jan 30 '20 at 00:33
  • Some progress. I believe the user did the Excel drag trick to add 1 day to each row - it probably messes something up. I manually overwrote 1/2/2019 02:30 and it corrected... Question remains though, any way to simply take the value of the cell? Changing it to string is not an option - requirement that it must be a date cell with custom format "d-mmm-yyyy h:mm" ... Real users would probably not do the drag trick as departure times will change day to day, but it would be good to code for it just in case. – Aaron Jan 30 '20 at 00:38
  • You should put those comments in your question, especially the second one that is the key to reproduce your issue. – krlzlx Jan 30 '20 at 14:58

1 Answers1

1

You're not the only one that have faced this problem. Here's a good answer.

You can use the DateUtil.GetJavaDate coming with NPOI to resolve this issue . You can create this basic extension method:

public static class Extensions
{
    public static DateTime DateCellValueRounded(this ICell cell)
    {
        return DateUtil.GetJavaDate(cell.NumericCellValue, false, TimeZone.CurrentTimeZone, true);
    }
}

Then use it like this:

DateTime date = row.GetCell(index).DateCellValueRounded;

GetJavaDate signature (the last parameter set to true does the job):

public static DateTime GetJavaDate(double date, bool use1904windowing, TimeZone tz, bool roundSeconds);
krlzlx
  • 5,752
  • 14
  • 47
  • 55