Inaccurate convertion of decimal to hours

Question

Guys please look at this code, I cant find what is wrong. I am trying to convert decimal value to hours and minutes, but it seems inaccurate.

        Dim selReleased As New SqlDataAdapter("SELECT SUM(TotalHours) AS ProcessingTime FROM TimeConsumed WHERE ClientID ='" & ClientAccountStatusViewer.txtClientID.Text & "'", jonsqlcon)
        Dim setReleased As New DataSet
        selReleased.Fill(setReleased)
        Dim txtPtime As String
        Dim ProcessingTime As Decimal

        txtPtime = setReleased.Tables(0).DefaultView.Item(0).Item("ProcessingTime")
        ProcessingTime = Math.Round(Convert.ToDecimal(txtPtime), 2)

        Dim pTime As String = String.Format("{0}:{1}", CInt(ProcessingTime), (ProcessingTime Mod 1) * 60)

        MessageBox.Show("Time Consumed: " & pTime, "RELEASED", MessageBoxButtons.OK, MessageBoxIcon.Information)

when the code is executed it will sum up the TotalHours of all record in the database as a ProcessingTime, then after that it will execute the formula I used which is String.Format("{0}:{1}", CInt(ProcessingTime), (ProcessingTime Mod 1) * 60) but it seems in accurate.

The sum of TotalHours is: 0.63 so it is more than 30 minutes.

But if you look at the Table From the TimeStart of the first record to TimeStopped of the last record didn't match the TotalHours which is 0.63 and approximately 18-19 minutes. Any help will be much appreciated. Thanks in Advance!

For reference please look at this table:

ClientID        TimeStart               TimeStopped             Officer          StageProcess       TotalHours      Status
UO.1802.0002    2/13/2018 1:52:16 PM    2/13/2018 1:53:18 PM    Jeff Olive       Check List         .01747  
UO.1802.0002    2/13/2018 1:53:18 PM    2/13/2018 1:53:23 PM    Jeff Olive       Application        .00153  
UO.1802.0002    2/13/2018 1:53:23 PM    2/13/2018 1:53:40 PM    Jeff Olive       Occular            .00482  
UO.1802.0002    2/13/2018 1:53:40 PM    2/13/2018 1:59:24 PM    jon ra           Remarks Step2PQ    .09558          For Verification
UO.1802.0002    2/13/2018 1:59:24 PM    2/13/2018 2:00:14 PM    Jeff Olive       Occular            .1143   
UO.1802.0002    2/13/2018 2:00:14 PM    2/13/2018 2:01:08 PM    jon ra           Remarks Step2PQ    .12455  
UO.1802.0002    2/13/2018 2:01:08 PM    2/13/2018 2:02:11 PM    Jayson Tadeo     CIR                .0176   
UO.1802.0002    2/13/2018 2:02:11 PM    2/13/2018 2:02:32 PM    Jayson Tadeo     AR                 .00596  
UO.1802.0002    2/13/2018 2:02:32 PM    2/13/2018 2:03:20 PM    jon ra           Remarks Step4PV    .01351          For Verification
UO.1802.0002    2/13/2018 2:03:20 PM    2/13/2018 2:04:19 PM    Jayson Tadeo     CIR                .01651  
UO.1802.0002    2/13/2018 2:04:19 PM    2/13/2018 2:04:27 PM    Jayson Tadeo     AR                 .00233  
UO.1802.0002    2/13/2018 2:04:27 PM    2/13/2018 2:05:09 PM    jon ra           Remarks Step4PV    .04373  
UO.1802.0002    2/13/2018 2:05:09 PM    2/13/2018 2:06:27 PM    Jeff Olive       CRAM               .02182  
UO.1802.0002    2/13/2018 2:06:27 PM    2/13/2018 2:07:34 PM    jon ra           Remarks Step6CA    .01873          For Verification
UO.1802.0002    2/13/2018 2:07:34 PM    2/13/2018 2:08:26 PM    Jeff Olive       CRAM               .05493  
UO.1802.0002    2/13/2018 2:08:26 PM    2/13/2018 2:09:43 PM    jon ra           Remarks Step6CA    .05465  
UO.1802.0002    2/13/2018 2:09:43 PM    2/13/2018 2:11:13 PM    Jeff Olive       LoanDoc            .02505

[Is floating point math broken?](https://stackoverflow.com/q/588004/62576) — Ken White, Feb 14 '18 at 03:34

Ricardo González · Answer 1 · 2018-02-14T04:50:59.717

1

Why to reinvent the wheel?

When dealing with time, it is better to use TimeSpan class.

    ProcessingTime = Math.Round(Convert.ToDecimal(txtPtime), 2)

    Dim ts As TimeSpan = TimeSpan.FromHours(ProcessingTime)

    Dim pTime As String = ts.TotalDays.ToString("f0") & " day(s) " &
                    ts.Hours.ToString.PadLeft(2, "0"c) & ":" &
                    ts.Minutes.ToString.PadLeft(2, "0"c) & ":" &
                    ts.Seconds.ToString.PadLeft(2, "0"c)

pTime = "0 day(s) 00:37:48", as simple as that. But you can teak it however you want.

edited Feb 14 '18 at 04:50

answered Feb 14 '18 at 03:42

Ricardo González

1,385
10
19

You might want to try `ts.TotalHours.Tostring("f0") & ":" & ts.Minutes.ToString.PadLeft(2, "0"c)` so it is closest to the math you intended to do – Ricardo González Feb 14 '18 at 03:56
I got the same result. But thanks for the tip. I think I have found the problem. and it is not in this code. anyways thanks for the response. till next time :) – Feb 14 '18 at 04:06
I think the data in your database is incorrect. How is the TotalHours column calculated? Paste it into Excel and do some testing. – Mary Feb 14 '18 at 04:46

Inaccurate convertion of decimal to hours

1 Answers1