Summing Working Hours in a Day(DATATABLE)

[rockchord]

HI

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

May i use datatable without excel?THX~

[Anil_G (Anil Gorthi)]

@rockchord

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

Cheers

[rockchord]

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~

[Gokul_Jayakumar (Gokul Jai)]

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

How to add the Time Duration in Excel?

Hello UiPathans! Check out this tutorial to know about the sum of Durations in excel of DT. Here are the steps Read the Excel sheet using the Read range activity. Create a variable with the System.TimeSpan Variable type. and assign 00:00 [image] Now Loop the process Using For Each DataTable. Here we have to identify the format of duration stored in excel by using write line activity. [image] [image] 4.1 In this 12/30/1899 08:20:00 we get in the format of “MM/dd/yyy…

Regards,
Gokul Jayakumar.

[supermanPunch (Arpan)]

@rockchord

(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

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.

[rockchord]

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~

[supermanPunch (Arpan)]

@rockchord

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

@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.

[rockchord]

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

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

[supermanPunch (Arpan)]

@rockchord

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

@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

@rockchord

Object reference not set to an instance of an object.”

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.

[rockchord]

@supermanPunch

strSu

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.

[supermanPunch (Arpan)]

@rockchord

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

@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 +
02:05:07
*********
08:05:08

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.

[rockchord]

@supermanPunch

observe

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

the output as below:
Name,Duration,TotalHours
A,06:00:01,8.09
A,02:05:07,NA
B,12:33:54,12.56
C,09:15:21,10.31
C,01:03:05,NA

How to display the correct number?THX~

[supermanPunch (Arpan)]

@rockchord

How to display the correct number?

@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

[rockchord]

@supermanPunch

(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.

[rockchord]

@rockchord

B,12:33:54,12.56

the output as below:
Name,Duration,TotalHours
B,12:33:54,13

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”)

TotalHours->Hours

the output as below:
Name,Duration,TotalHours
B,12:33:54,12

Is this modification correct?

[supermanPunch (Arpan)]

@rockchord

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”)

@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.

[rockchord]

@supermanPunch

Hence that was chosen.

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