Count time between 2 fields

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:

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

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.

datetime-diff.transform (5.3 KB)


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

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

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

Many Thanks

Here is my solution as per your requirement.

Transform file.
CountTimeBetween2Fields.transform (2.7 KB)


loved the use of date time to remove dot

1 Like