Summing Working Hours in a Day(DATATABLE)


I get something from ths post(Summing Working Hours in a Day)

May i use datatable without excel?THX~


Welcome to the community

You can read the data into datatble and do the manipulations yes…

If something else is the question then please be elaborate and show what is needed, that would help


Thanks for your reply.
I have this table:

Name Duration
A 06:00:01
A 02:05:07
B 12:33:54
C 09:15:21
C 01:03:05

and I want the results to look like this:

Name Duration Total
A 06:00:01 08:07:08
A 02:05:07 NA
B 12:33:54 12:33:54
C 09:15:21 10:18:26
C 01:03:05 NA

And use the following code

(From d In DT.AsEnumerable
Group d By k=d(“Name”).toString.Trim Into grp=Group
Let A = New Object(){k, grp.Sum(Function(x) Convert.ToDouble(x(“Duration”).toString.Trim))}
Select strSum.Rows.Add(A)).CopyToDataTable

But get an error as: Input string was not in a correct format.
how can i fix ㄍthe code?THX~

Hello @rockchord
Kindly refer to this thread it will heps you.

Gokul Jayakumar.

1 Like

Hi @rockchord ,

The above Expression although it was close to the near solution required, It would only produce the Grouped First row in the Output and will not add the remaining rows and update the column value of Total as "NA".

To Perform this we would also need to iterate through the grouped rows and update the group as required for the Output Datatable.

Also, For TimeSpan values addition we would need to convert it to Ticks and then convert the total ticks value to a Timespan value.

Check the below Expression :

(From d In DT.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
From g In grp.DefaultIfEmpty
Let A = New Object(){k, g("Duration").toString.Trim,If(g.ItemArray.SequenceEqual(grp.First.ItemArray),(New TimeSpan(grp.Sum(Function(x) TimeSpan.Parse(x("Duration").toString.Trim).Ticks))).ToString("g"),"NA")}
Select strSum.Rows.Add(A)).CopyToDataTable

Here, It is also assumed that the strSum is a Datatable variable and also contains the additional Column Total added to it before this operation.

1 Like

Thanks for your reply.
but i still got an error like as “Object reference not set to an instance of an object.”

Sorry i edited the DATATABLE …
If the datatable looks like this

Name Duration TotalHours
(String) (TimeSpan) (Int)

How can I modify the code?THX~

@rockchord ,

Could you let us know if you are also maintaining the types as mentioned above ? Also, Let us know if there would be any blank values in the Duration Column.

More precisely, if you could provide the Datatable data used that gives out the error we could pinpoint the error.

Name------ Duration------TotalHours

Not sure if this information is enough?
This computer cannot take screenshots…

@rockchord ,

From the above provided Screenshot, we see that you would require the total hours (as Number not as Timespan format). Considering that, we could modify the above expression as below :

(From d In DT.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
From g In grp.DefaultIfEmpty
Let A = New Object(){k, g("Duration").toString.Trim,If(g.ItemArray.SequenceEqual(grp.First.ItemArray),Math.Round((New TimeSpan(grp.Sum(Function(x) TimeSpan.Parse(x("Duration").toString.Trim).Ticks))).TotalHours,2).ToString,"NA")}
Select strSum.Rows.Add(A)).CopyToDataTable

But if the data is as provided above then there shouldn’t be an error as you have mentioned.

Make sure the strSum datatable is declared and intialised before this Expression is used.

Sorry…It was my fault
strSum datatable is declared and intialised~

I got an error:Input string was not in a correct format.Couldn’t store <8.09> in TotalHours Column. Expected type is Int32.
Begging for your help again.

@rockchord ,

It seems that you are Keeping the Type of the Datatable column as Integer. But in this case, you would lose some values. If you can observe in the Datatable value,

06:00:01 +

If we round up the value (08) we will lose the 5 minutes value. Is that Ok for your case ?

Try Changing the Type of the Column to Object and Check.

Let us know if the Output is not as expected.

If I modify the Type of the Datatable column(TotalHours) as string or object.

the output as below:

How to display the correct number?THX~

@rockchord ,

For your case, Is the Correct Number 08 or 8 ? Not 8.09 ?

If that is the case , then Check the below Modified Expression :

(From d In DT.AsEnumerable
Group d By k=d("Name").ToString.Trim Into grp=Group
From g In grp.DefaultIfEmpty
Let sumValue = If(g.ItemArray.SequenceEqual(grp.First.ItemArray),CInt(Math.Round((New TimeSpan(grp.Sum(Function(x) TimeSpan.Parse(x("Duration").toString.Trim).Ticks))).TotalHours)).ToString.PadLeft(2,"0"c),"NA")
Let A = New Object(){k, g("Duration").toString.Trim,sumValue}
Select strSum.Rows.Add(A)).CopyToDataTable

Either 08 or is correct.
It works fine.
Thank you very much for your assistance.

1 Like

the output as below:

the value of totalhours should be 12.
How to display the correct number?

If i modified the code.
Let sumValue = If(g.ItemArray.SequenceEqual(grp.First.ItemArray),CInt(Math.Round((New TimeSpan(grp.Sum(Function(x) TimeSpan.Parse(x(“Duration”).toString.Trim).Ticks))).TotalHours)).ToString.PadLeft(2,"0"c),“NA”)


the output as below:

Is this modification correct?

@rockchord ,

What was the modification you have done ?

Also, let us know if 12:33:54 → 12 hrs, what would be the total hours for 12:54:32 ?

The current reason for setting the value as 13 is because, the The Total hours is closer to 13 than 12. Hence that was chosen.

1 Like

Thank you so much for your clear and unambiguous explanations on this topic.
They have helped me tremendously.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.