Get the duration of my RPA workflow and add the latest duration to it from Excel

Hello UIPath community,

After trying hundred of ways to do it I still can’t figure out how to get the total duration of all RPA execution …

My current workflow do that :

  • [Assign activity] DTime_Start = DateTime.now()
  • […]
  • [Assign activity] DTime_Stop = DateTime.now()
  • [ReadCell activity] get duration value from Excel to DTime_Backup
  • [Assign activity] GV_TotalDuration = (DTime_Stop - DTimeStart) + DTime_Backup

but it’s not working this way …

DTime variables are DateTime type
GV variable is GenericValue type

Please don’t provide me any workflow files, I can’t open it on this computer.
Also I’m working on UIPath 2021.10.5 and I can’t update it because of my company policy.

Hi @hubert_leleu

Check out this thread

Regards
Gokul

thanks for your help, but what if my total duration is more than 24h ?

It will subtract the Start and End and give the Output

Get the total duration is not the most difficult, my issue is more about how to add up the value to the already existing value of my excel file.

Hi @hubert_leleu ,

I am assuming you are using Write Cell Activity to write the Total Duration to Excel cell ?

But could you also provide details of the data types of the variables used ? And Let us know how is the format of Total Duration (Total Days or Total Hours or Total Minutes) in the Excel.

To make it simple I just want to store the duration time of the RPA in Excel using WriteCell activity.

But I also expect that, each time I’m running the RPA, the duration will be adding to the last one already stored in Excel.

So far I’m able to calcule the duration of the RPA execution using TimeSpan with the following format : “HH:mm:ss”

but I don’t know how to add this duration to the one contained in Excel
(same format)
and I don’t know neither what to do if the total duration is above 24h.

All kind of help is welcome

Hi @hubert_leleu ,

If the value in Excel is of the format as mentioned, then we could give the following a try :

GV_TotalDuration = Timespan.Parse(DTime_Backup.ToString).Add(DTime_Stop-DTimeStart).ToString

Example :
image

Here, we could map the Inputs as the following :
DTime_Backup"23:12:00"
DTime_StopNow
DTimeStartNow.AddDays(-1)

Notice the 1.23 as the Output, indicating that A Day and 23 hours as the duration.

Let us know if you were able to try the above method and also provide a feedback. If an error occurs do send the Error Message.

Thanks a lot for the help @supermanPunch I tried it on my side with some adaptation and it seams it’s working !!

just two last things please, currently I got my result as : 06:00:05.11345
How can I format it to round it like : 06:00:05 ?

Also do you know if this format is possible : dd:HH:mm:ss ?
so thanks to that I can have : 01:23:12:00 instead of 1.23:12:00 like in your exemple.

thanks again

@hubert_leleu ,

We could use the format specifier "g" to convert into the format that is required. For Removing the Milliseconds we can perform a Split based on . and take only the first split.
image

TimeSpan.Parse("23:12:00").Add((Now-Now.AddDays(-1).addMilliseconds(12))).ToString("g")
Split(TimeSpan.Parse("23:12:00").Add((Now-Now.AddDays(-1).addMilliseconds(12))).ToString("g"),".")(0)

Note : AddMilliseconds() is used just to demonstrate.

Let us know if this works for your case.

Thanks a lot for this last message @supermanPunch, It’s very usefull.
When I remove the .AddDay and AddMiniSecond everything seams working well.
But I still have some questions please :

After this process the RPA write the value in Excel with the following format : d:h:mm:ss

this format is ok for me so far, unfortunately I have to calculate the sum of the results obtained by the RPA using an excel formula that does not seem to recognize this format.

the formula is just : C1 + C2 + Cx
where Cx is using the “Time” type, so the format : hh:mm:ss

Any idea of what I can do to fix that ?

@hubert_leleu ,

What are C1 and C2 types ? Is it also the Time duration in the format dd:hh:mm:ss ?

What is the error received when performing the addition ?

C--Users-33672-AppData-Local-Packages-BlueMail.BlueMailEmail_t08282y3j4hc4-LocalCache-Roaming-BlueMail-Content-0-Inline-_48_46_48_95_52_50_120_110_46_48_95_52_50_120_110-image001

Here is a screenshot of my column.
Each row is the result of the RPA for differents teams.

The formula is not working because of the format of the first row.
other rows are empy so far so the format is still the normal one but they are exactly in the same situation of the first row.

Current formula is : =SUM(Value(C1);Value(C2);Value(Cx))

@hubert_leleu ,

I am not so sure, if the Excel could be able to compute the sum of those type of values. Could you let us know what is the Format of the values ? General or Date or ?

We could perform the Computation if we have/retrieve the values to the Studio. Maybe using Read Range Activity to get the Data as a Datatable, then computing the Total Duration of values present in that column and then using this Total value to Write to the Appropriate Cell using Write Cell Activity.

Computing the Total :

TimeSpan.FromTicks({"1:23:20:10","23:30:08"}.Sum(Function(x)TimeSpan.Parse(x).Ticks))

image

When used with Datatable, it will be Similar to this :

totalDuration = Split(TimeSpan.FromTicks(DT.AsEnumerable.Sum(Function(x)TimeSpan.Parse(x("Temps (JJ/hh/mm/ss)").ToString).Ticks)).ToString("g"),".")(0)

Here, DT is the datatable read from Read Range Activity, totalDuration is a String type variable.

Let us know if you could try this approach or would need to stick to the Formula approach.

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