Reading value from excel file, getting incorrect value

Question

I am reading excel file which contains column name time which consists time for example 14:10:25 , for some file, my code is working fine but for most of the files, when I read the column, it returns these random floating values "0.402777777777778"

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(excel_file_path.Text);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
string Time = xlRange.Cells[2, 1].Value2.ToString();

Time would be stored as a number. (for example, `14:10:25` is `0.59056712962963`) This is likely stored as text that you need to *convert* to a number/time. — ashleedawg, Oct 04 '18 at 06:59
Time is calculated as [seconds since midnight / seconds in day] so 14h10mn25s = (14*3600 sec + 10 * 60 sec + 25 s ) / (3600 * 24) — Frenchy, Oct 04 '18 at 06:59
Possible duplicate of [Reading Datetime value From Excel sheet](https://stackoverflow.com/questions/4538321/reading-datetime-value-from-excel-sheet) — ashleedawg, Oct 04 '18 at 07:00
*my code is working fine* => means sometime you get `14:10:25` and sometime you will get `0.402777777777778`? — er-sho, Oct 04 '18 at 07:01
yes .. but what i want is 14:10:25 or is it possible to convert 0.402777777777778 back to 14:10:25 — siddstuff, Oct 04 '18 at 07:02

score 1 · Accepted Answer · answered Oct 04 '18 at 07:24

You need to check your input string is in numeric format or not. and then convert your input to specific time if it is in numeric format.

Here i create an console app for your demonstration purpose.

class Program
{
    static void Main(string[] args)
    {
        string input1 = "0.402777777777778";
        double outVal = 0;

        if (double.TryParse(input1, out outVal))
        {
            var t = DoubleToTimeSpan(outVal);
            Console.WriteLine("Input: " + input1);
            Console.WriteLine("Output: " + t);
        }
        else
        {
            //Your string is in time format
        }

        Console.WriteLine();

        string input2 = "0.59056712962963";

        if (double.TryParse(input2, out outVal))
        {
            var t = DoubleToTimeSpan(outVal);
            Console.WriteLine("Input: " + input2);
            Console.WriteLine("Output: " + t);
        }
        else
        {
            //Your string is in time format
        }


        Console.ReadLine();
    }


    public static TimeSpan DoubleToTimeSpan(double dValue)
    {
        int seconds_in_a_day = 86400;
        int iDays = (int)dValue;
        double dSeconds = Math.Floor(seconds_in_a_day * (dValue -
        iDays));
        return new TimeSpan(iDays, 0, 0, (int)dSeconds, 0);
    }
}

Output:

Reading value from excel file, getting incorrect value

1 Answers1