How to add the Time Duration in Excel?

News Tutorials
, , ,
1

Hello UiPathans! :robot:

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

image

  1. Now Loop the process Using For Each DataTable.

  2. 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/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

image

  1. Sum the values using the assign activity

image

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

  1. 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. :expressionless:

image

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)

image

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

Best Regards
Gokul Jai

4 Likes