Excel / C# - Time String Being Interpreted as Double?

Question

I am writing a program that pulls data from a CSV file (which, due to the structure, is easier to work with through Excel). There are columns that hold a date and time. The date column processes correctly, yet the time column (F) is being interpreted as a double. For example, in the following loop, it sees the value as 0.00 on the first loop, 0.25 on the second, 0.26041666666666669 on the third, 0.27083333333333331o on the fourth iteration, and so on.

for (i = startRow; i <= endRow; i++)
        {
            PeriodSales saleRow = new PeriodSales();


             DateTime saleDate = Convert.ToDateTime((sheet.Cells[i, 5] as Excel.Range).Value);
                var timeString = (sheet.Cells[i, 6] as Excel.Range).Value;
                DateTime timeOfSale;

                timeOfSale = new DateTime(saleDate.Year, saleDate.Month, saleDate.Day, 0, 0, 0);

               // the lines below were commented out for testing purposes
 (so I could see the value of timeString in the loop */


/* if (timeString != "0")
                        {
                            String[] timeArray = timeString.Split(':');
                            timeOfSale = new DateTime(saleDate.Year, saleDate.Month, saleDate.Day, Convert.ToInt32(timeArray[0]), Convert.ToInt32(timeArray[1]), 0);
                        }
                        else
                        {                   
                        timeOfSale = new DateTime(saleDate.Year, saleDate.Month, saleDate.Day, 0, 0, 0);
                        } */

Attached is a screenshot of my spreadsheet/CSV

The underlying CSV (in Notepad++)

Thanks for any guidance.

Can you try something like this: `Console.WriteLine(TimeSpan.FromDays(0.003313));`, where `0.003313` is your double variable? — Vityata, Mar 01 '17 at 16:36
Why is it easier to work with in Excel than it is a dedicated CSV parsing libary such as CsvHelper? — mason, Mar 01 '17 at 16:39
@mason, the file (which is largely pulled from a third party) is poorly structured. There is no set amount of columns and the header rows are just another line (albeit mark with in H instead of in the third column). Believe me I would much rather work with as a CSV, but given the document's structure and the data I need, it would probably be extremely complicated. — KellyM, Mar 01 '17 at 16:44

score 2 · Answer 1 · edited May 23 '17 at 12:31

2

This discussion might answer your question:

Capturing Time Values from an Excel Cell

The first answer there may be what you're looking for. There may be some additional C# you're going to need to write to get the conversions you're looking for.

edited May 23 '17 at 12:31

Community

1
1

answered Mar 01 '17 at 16:30

MJR

169
2
15

score 1 · Accepted Answer · answered Mar 01 '17 at 16:33

1

You want to use timeOfSale = DateTime.FromOADate(timeString).TimeOfDay; which will convert the date using excel format

answered Mar 01 '17 at 16:33

Treziac

3,056
1
17
19

Thanks much. I was able to get it working with the following: DateTime timeOfSale = saleDate + System.DateTime.FromOADate(timeString).TimeOfDay; – KellyM Mar 01 '17 at 16:41

Excel / C# - Time String Being Interpreted as Double?

2 Answers2