Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# How to calculate average time in VB .net

Posted on 2012-08-28
Medium Priority
1,734 Views
Hello Experts,

I have an application that loads a dataGridView.  One of the columns is a time field in the format HH:MM:SS.

The time data is stored in an SQL server DateTime field and formatted with this line:

DGV1.Columns("Elapsed Time").DefaultCellStyle.Format = "HH:mm:ss"

Now users have requested that I add a label to the form that shows the average elapsed time for all of the rows displayed in the DGV.

How would I do such a thing, I'm not even sure where to start.

Thanks
0
Question by:Steve5140
• 6
• 6
• 4

LVL 83

Accepted Solution

CodeCruiser earned 1600 total points
ID: 38341462
I think you would need to convert HH:mm:ss to either just minutes or just seconds depending on the level of accuracy required. Then calculate average and convert the average back to HH:mm:ss format.
0

LVL 13

Expert Comment

ID: 38341476
Maybe your easiest way of doing it is on the server side.
Give this a read and let us know if it works for you.

0

Author Comment

ID: 38342189
OK,  This may be a little clunky but I've got the all the time entries into a total number of seconds, and then I've divided the total seconds by the number of entries.

``````Dim TotalTime As TimeSpan
Dim TimeEntry As DateTime

For i = 0 To DGV1.Rows.Count - 1
TimeEntry = DGV1.Rows(i).Cells("Elapsed Time").Value
TotalTime += #1/1/1900# - TimeEntry
Next

Dim x As Decimal = TotalTime.TotalSeconds

x = x / DGV1.Rows.Count
``````

So how would I convert the average number of seconds into HH:MM:SS ?
0

LVL 83

Expert Comment

ID: 38342215
Divide by 60 to get minutes and then again to get hours and then just combine them together into a string.
0

LVL 13

Assisted Solution

LIONKING earned 400 total points
ID: 38342304
If you already have the total of seconds you can do this:

Dim seconds As Integer = 34500
Dim ts As TimeSpan = TimeSpan.FromSeconds(seconds)

Then you can use:
ts.Days, ts.Hours, ts.Minutes, ts.Seconds

Let us know.

Just replace the datatype and value of the seconds variable accordingly.

0

Author Comment

ID: 38345213
Thanks to both of you I finally got this working.  Some of the delay was because of data problems that threw me off track.

Here is the final code:

`````` Private Sub CalcAvgTime()

If DGV1.Rows.Count = 0 Then Exit Sub

Dim TotalTime As TimeSpan
Dim TimeEntry As DateTime

For i = 0 To DGV1.Rows.Count - 1
TimeEntry = DGV1.Rows(i).Cells("Elapsed Time").Value
TotalTime += TimeEntry - #1/1/1900#
Next

Dim Secs As Decimal = TotalTime.TotalSeconds
Secs = Secs / DGV1.Rows.Count

Dim AvgTime As TimeSpan = TimeSpan.FromSeconds(Secs)

lblAvgTime.Text = "Average call time = " & String.Format("{0:00}:{1:00}:{2:00}", AvgTime.Hours, AvgTime.Minutes, AvgTime.Seconds)

End Sub
``````

LionKing - I didn't want to do this on the server side because my app allows the user to filter the results in the DGV.  Therefore I needed to be able to update the average time as the filter changed.
0

LVL 83

Expert Comment

ID: 38345375
The main question was calculating average time not formatting timespan right?
0

Author Comment

ID: 38345391
Whoops, I think I know what you are getting at - that assignment of points ?

Is there a way for me to edit the points assignment ?
0

LVL 83

Expert Comment

ID: 38345482
You would have to use Request Attention button to ask moderators.
0

Author Comment

ID: 38345511
CodeCruiser,

I will persue this.  I applogize, but I was exited to get a solution and probably acted to quickly.  I will see about a more equitable split.

-Steve
0

LVL 13

Expert Comment

ID: 38345533
CodeCruiser is correct, he deserves the points.
My contribution was for something out of question, therefore the points should go to him.

0

Author Comment

ID: 38345605
Thanks Lionking - I appologize to both of you.

I did send a message to the moderators, but I asked them to split the point 50/50 so I still haven't fixed this correctly.

Moderators - Per Lionkings message above, please assign all points to CodeCruiser

-Steve
0

LVL 83

Expert Comment

ID: 38345806
50/50 would be fine.
0

Author Comment

ID: 38345976
Wow, I hate to make this more complicated, but CodeCruiser says a 50/50 split would be fine, Lionking says all points can go to CC.

How about a compromise of 400 to CC and 100 to LK ?

I want to be fair, and I want to reward both of you for helping.
0

LVL 83

Expert Comment

ID: 38346013
Fine with me
0

LVL 13

Expert Comment

ID: 38346017
Steve,

Since the help I provided was for something outside the initial question, I assume that "technically" I don't deserve points. That's why I said all points should go to CodeCruiser.

So 0 points or "any" is appreciated, don't worry about that.

Thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of convertingâ€¦
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exchâ€¦
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give iâ€¦
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month15 days, 3 hours left to enroll