How to add the Time Duration in Excel?

[Gokul_Jayakumar (Gokul Jai)]

Hello UiPathans!

Check out this tutorial to know about the sum of Durations in excel of DT.

What is meant Duration?
1: continuance in time gradually increase the duration of your workout.

2: the time during which something exists or lasts there for the duration of the concert
.
Example: Total Working Hours of the employee.

Here are the steps

1. Read the Excel sheet using the Read range activity.

2. Create a variable with the System.TimeSpan Variable type. and assign 00:00

3. Now Loop the process Using For Each DataTable.

4. Here we have to identify the format of duration stored in excel by using write line activity.

4.1 In this 12/30/1899 08:20:00 we get in the format of “MM/dd/yyyy HH:mm:ss”

4.2 We need duration only, so using regex, we match the necessary string and extract the data.

System.Text.RegularExpressions.Regex.Match(CurrentRow("Hours").ToString,"\d+:\d+:\d+").ToString

5. Sum the values using the assign activity

5.1 Convert String to Time span, System.TimeSpan.Parse(Timestr)

6. Outside the loop we can print the Time value. In this stage, we found bugs where the time span calculates 24hrs as 1 day, for example, 25:00 hrs shows a 1.1:00hrs.

To avoid this, use the Below Expression

String.Format("{0:00}:{1:00}:{2:00}", math.Floor(WorkingHours.TotalSeconds / 3600), (WorkingHours.TotalSeconds / 60) Mod 60, WorkingHours.TotalSeconds Mod 60)

Hope this helps
Kindly refer to this XAML file
Forum_Excel_Add_Duration.zip (77.5 KB)

Best Regards
Gokul Jai