Values vary after conversion from excel

Question

I am trying to get values from one of the column from excel and i am facing strange issue that i cannot overcome so far.

Excel cells text we working on

Column format is set to: [h]:mm:ss so means hours could exceed 12/24.

When i am getting that values they are in double format as excel probbaly stores it in that way therefore i decided to write function to convert it back again to hours, minutes and seconds so i did that function:

   Public Shared Function parseExcelHour(cellInput As String) As String
    Dim excelHour As Double = 0
    Dim hour As Integer
    Dim min As Integer
     Dim sec As Integer
    Try
        excelHour = [Double].Parse(cellInput)
    Catch
    End Try
    sec = CInt((excelHour * 1440 * 60) Mod 60)
    min = CInt((excelHour * 1440) Mod 60)
    'mod (%) takes only the remainder as an int (if 5/4 = 1.25 , % only takes the number 1 that cannot be divided into an integer)
    hour = CInt((excelHour * 1440) / 60)
    ' with the int cast you get only an integer.
    Return hour & ":" & min & ":" & sec
End Function

However when i see the results, they are vary between excel and what i get after conversion. For three of them hours are either -1 or +1 if you compare. Also in one case we have additional + 1 minute. I suppose there is wrong hour calculation but i could be in wrong. See on screeshoot:

Results

Does anyone knows why i got those differences? Is that because i am missing something within my method or something else.

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

Excel stores a full Datetime equivalent as one double. The part before the decimal point is the days (since 1.1.1900; 1.1.1904 on Mac; note the bug that 1900 is faultily cosnider a leap year in Excel).

The part after is the time of the day, wich is what you apparently want: https://stackoverflow.com/a/981865/3346583

What you are seeing in excel is meerely a ToString Foramting of the double value. Same way DateTime.ToString() would give you a string representation of whatever value is actually stored (most often a realy big unsigned int, with the ticks since a date).

A difference in full hours sounds like it might be a Timezone issue. But I am not aware that Excel stores Timezones in the first place (or what default timezone it asumes).

edited May 23 '17 at 12:32

Community

1
1

answered Mar 31 '17 at 09:08

Christopher

9,634
2
17
31

but what then i have to change to make it works? Could you show? – dev Mar 31 '17 at 09:10
1 hour difference just screams "Timezone issue" at me. But Excel has no support for timezones. If you want to approximate it, you have to add a certain number of hours to the underlying value: https://www.quora.com/In-Excel-how-do-I-convert-data-into-a-different-timezone Are you certain you are looking at the cell with the raw value and not a formula doing some Timezone adatation or similar addition of hours? – Christopher Mar 31 '17 at 09:15
@I think that's because first of all excel is taking the value from the cell so in this case it will be datetime then it shows it's double representative value so means for example 7.234234242 means we have to extract 7 days * 24 hours but also keeping in mind after dot we have also hours that somehow could be added to the 7*24 + remaining hours after dot. That's my way of thinking but have no idea how to implement that in the function. Regarding timezone it's not the case i suppose it's more like calculation what i just wrote. – dev Mar 31 '17 at 09:19
when i used .Text instead of .Value i get exactly same as in excel. Should i go this approach instead of getting double value? Does .Text is always gives me what i see in the cell? Seems yes. But not sure if i should go into .Text. Heard something about issues using it. – dev Mar 31 '17 at 09:47

Values vary after conversion from excel

1 Answers1