How to find the sum of column(Total time)

Help Studio
, ,
1

Hi,

I have an excel I have to find the sum of the column “Total time”, It consist of hours spend in the workspace.so I want to find the grand total of that particular column.
DataExcel.xlsx (10.2 KB)
Can any one please explain how to find the sum.

2

Hi @Chippy_Kolot

You can try like this.

  1. read the data of column Marks and store the output in datatable name OutPutDT.
  2. Use below assign activity to get sum.
    SumData = OutputDt.AsEnumerable.Sum(function(x) Convert.ToDecimal(x(“Marks”)))

Make sure the SumData variable is in Object datatype.

Regards!! It may help you.

1 Like
5

Hi @Chippy_Kolot ,

Step1: Read the file as InputDT
Step2: Use Assign Activity

as per your input Total time will (Reporting time - Difference). For this case you can use below expression o/p = 86

sumOfTotalTime = cdbl(inputDT.AsEnumerable.SUM(Function(x) If(IsNumeric(x("Total time").tostring),convert.ToInt32(x("Total time").ToString),0)))

For below case i have converted datetime and then sum all values. o/p = 146

sumOfTotalTime =

 cdbl(inputDT.AsEnumerable.SUM(Function(x) If(IsNumeric(x(“Total time”).tostring),Cdbl(x(“Total time”).ToString),0)))+
cdbl(inputDT.AsEnumerable.SUM(Function(x) If(Not IsNumeric(x(“Total time”).tostring),Cdbl(TimeSpan.Parse(convert.ToDateTime(CurrentRow(“Total time”).ToString).tostring(“hh:mm:ss”)).Hours),0)))

data type of sumOfTotalTime should be System.Double

Thanks!

6

Hi @Chippy_Kolot

Try this

Step 1: Read range Activity → read the File (Note: Enable preserve format in the properties)

If you not Enable the property The value is not consider for timespan

image
image1182×331 23.9 KB

Step:2: use Assign Activity
LeftSide: Total Amount(Variable type:Int32)

Value side:

1.To calculate Total Hours

Dt_input.AsEnumerable().select(Function(x) (x("Total time").ToString)).ToArray().AsEnumerable().Sum(Function (item) If(item.Contains(":"),TimeSpan.Parse(item).Hours,TimeSpan.FromHours(CDbl(item)).Hours))

image
image909×211 11.6 KB

**2. To calculate Total minutes **

Dt_input.AsEnumerable().select(Function(x) (x("Total time").ToString)).ToArray().AsEnumerable().Sum(Function (item) If(item.Contains(":"),TimeSpan.Parse(item).Minutes,TimeSpan.FromHours(CDbl(item)).Minutes))

image
image1004×196 11.6 KB

Thank you
VP

7

Hi @Chippy_Kolot

Try to use this query to get the sum of hours for column “Total Time”

inputDT.AsEnumerable().sum(function(x) if(IsDate(x("Total Time").ToString),cint(DateTime.parse(x("Total Time").ToString).Hour),cint(x("Total Time").ToString)))

thanks
Priya