Count time between 2 fields

Question
1

Hi, I have 2 fields which normally in excel I would minus one from the other. but it doesnt seem to work for me in easy data.

The field format for both columns is below:

START_TIME END_TIME
12/11/2023 1:49:21. AM 12/11/2023 1:49:22. AM

I expect an output of 00:00:01 seconds as a column entitled DURATION

2

The Calculate subtract function works on dates, but not datetimes. So you need to:

  • remove the extra ‘.’ using Replace
  • convert to an ISO Datetime using DateTime Format

Then you can either use the DateTimeToMSecs function of Calculate or Javascript to calculate the difference in Seconds.

image
image1536×395 58.5 KB

datetime-diff.transform (5.3 KB)

2 Likes
3

I added something to show the difference in days and time:

image
image744×83 16.1 KB

image
image1437×378 80.1 KB

datetime-diff_extended.transform (8.8 KB)

And yes, Excel is in that case easier, but if it is a productive often reused flow with big data, it works.

1 Like
4

Thanks Both this works really well. I actually prefer not to use excel so this is all good.

Many Thanks

5

Here is my solution as per your requirement.

image
image1034×356 36.9 KB

Transform file.
CountTimeBetween2Fields.transform (2.7 KB)

2 Likes
6

loved the use of date time to remove dot

1 Like