Comparing Lines with dates

Question
1

I have two columns with the following date/time format. They are shifts. They have a start time and an end time.

2021-08-11 15:00:00

I would like to subtract one from the other to find out the total hours of the shift.

Any ideas?

2

You can easily find the difference between 2 dates using Calculate with operation = Subtract:
https://www.easydatatransform.com/help/1j/windows/html/calculate.html

So you can:

  • separate out the date and time parts using Split Col
  • calculate the difference between dates in days
  • calculate the difference between times in hours ( which might involve splitting into hours and minutes)
  • total difference = 24 * difference in days + difference in hours

So doable, but long winded.

The other alternative is the Javascript transform, which can handle calculations like this:

return new Date( “2021-08-01T10:00” ) - new Date( “2000-01-01T00:00” );

You can use some text transforms to construct the dates in the appropriate format.

3

This .transform shows how you can do using Javascript:

diff-hours.transform (3.4 KB)

1 Like
4

A “FWIW” comment :I haven’t touched a line of code in more than 40 years. However, when there were some transforms that didn’t exactly fit, I started turning to the JAVA transform - and found a real gem, which really gears up EDT.

With the aid of JavaScript Tutorial it was pretty simple to add the aspects I needed, sometimes maybe by approaches that EDT might view as a bit odd e.g. for some unstructured XML.

So it’s worth checking this option if you get stuck, or something looks a bit unwieldly with the standard transforms.

1 Like
5

I have a feeling this may lead me down a rabbit hole!!! haha. thanks for this tho, if I get the time it will be useful!