Subtract one time period with the other obtained from Excel

Help Studio
, , ,
1

Hey,

In a excel table;
There are two rows-
login and logout.

I need the total time spent(i.e. logout - login )
and save it in the next new column(totalTime).
1Capture
I’m able to add the new column;
not able to subtract it.
can anyone help?

Thanks in advance!

2

Hi @ray_sha,

Try below code
Assign the values into two variable called ‘time1’ and ‘time2’ datatype as the string

time1 =  "7:00"
time2 = "16:00"

Create another variable called ‘duration’ datatype as TimeSpan
Assign it as below

duration = DateTime.Parse(time2).Subtract(DateTime.Parse(time1))

2 Likes
3
  1. Write cell B4 as with Formula B3-B2 .
  2. Auto fill range activity to fill rest of the rows.
3 Likes
4

Hey,
Source: Assign
Message: String was not recognized as a valid DateTime.

This is the error

5

@ray_sha, Ensure datatype of duration variable is string and it should work

6

Hi @ray_sha

Check the attachment

TimeDiff.xaml (4.9 KB)

Hope this helps

2 Likes
7

TimeDiffCalc.xaml (5.4 KB)

Please check the attached Xaml.

Hope it helps you

Both the values should be string initially and then it will give the differenct between two values as Datetime

8

Hey
I need it from the excel sheet; as given in the above query

9

Hey,
Source: Assign
Message: String was not recognized as a valid DateTime.
Then this will be the error

10

@ray_sha,

Source: Assign
variableName = DateTime.Parse(“16:00”).Subtract(DateTime.Parse(“7:00”)).ToString

Where datatype of variableName is String. (Replace string value with actual variable e.g. time1, time2)
I tried this and it is working for me.

Cheers

11

Hey I’m fetching the value from the excel file
1Capture

It must accept value from the excel file(ss of which is shown above)
and store the value in the new column (beside logout) as totaltime

12

Why cant you frame a values as below and check .
login :- 03/06/2020 07:00:00
logout :- 03/06/2020 16:00:00

I have used todays date ( Datetime.Now)

13

Hey,

If we give values directly then it works;
But I want to take the value from the excel file and thus its giving error

14

Hey,
can you please elaborate on that one?

15

Hey,
I didn’t understand you, can you please elobarate?

16

@ray_sha Can you Follow these Steps :

  1. Use Workbook Read Range Activity and read the Excel File, Get Output as Datatable.
  2. Use For Each Row Activity for the Output Datatable
    Inside For Each Row, Use a Message Box with value as row(“login”).ToString

Send the Screenshot of what Displays in the Message Box , Based on that value we can Apply what method is Necessary

1 Like
17

@ray_sha, Surprisingly it is working for me. Please check attached workflow and excel.
Attached workflow is for demonstration only you have to iterate through all rows as needed.

Main.xaml (5.2 KB) Test.xlsx (9.0 KB)

1 Like
18

Hey,
This is the output

login

19

@ray_sha Ok, That is Confusing :sweat_smile: , Can you Check what is the Data Type of logout and login in Excel File? Is it Date or Custom or Something Else ?

20

Hi Use the below and check pls

DateTime.ParseExact(“06/03/2020 18:00:00”, “MM/dd/yyyy HH:mm:ss”, System.Globalization.CultureInfo.InvariantCulture)-DateTime.ParseExact(“06/03/2020 08:30:00”, “MM/dd/yyyy HH:mm:ss”, System.Globalization.CultureInfo.InvariantCulture)).TotalHours.ToString()

1 Like