Date format for calculations

Question
1

What format does the date have to be in to do hour difference calculation.

Right now, I have it as 03/01/2022T6:45:00 PM as an example.

I looked at the post about the java script I could run where you have date 1 and time 1 in different columns. but it looks like the function merges them to 1 with the T in the middle and then does the subtraction. Why could I not get the date set up correctly first and do a simple calculation?

return ( new Date( $(date2) + ‘T’ + $(time2) ) - new Date( $(date2) + ‘T’ + $(time2) ) ) / ( 1000 * 60 * 60 );

I have shifts Im trying to understand. I have a start date/time and end date/time that im trying to subtract from each other to get hours worked.

The original data looks like this.

Start time: 3/15/2022 6:45:00 AM
End Time: 3/15/2022 6:45:00 PM

2

Easy Data Transform understands dates, but currently not datetimes.

The Javascript transform can handle datetimes, but expects them to be in this format:

2000-01-01T00:00

See also:

I’m not a Javascript expert, but I don’t think this is going to work:

new Date( $(date2) + ‘T’ + $(time2)

As Date is a value in milliseconds it doesn’t make sense to append a string to it.

You need to construct a string in the format “2000-01-01T00:00” and then pass it to Date.

3

See also:

4

I tried this path and can get it to work if I can take the time to get the time format into military. that just takes a very careful splitting and rejoining of the data. makes me nervous to tweak base data that much. any tricks for quick transforms to military time from something that looks like 06:45:00 AM?

5

You need to split it into its component parts and then use an If to add 12 hours if it is PM.

If you look on Stackoverflow there is probably a way to do this with a Javascript function as well.

6

Awesome. I did this via another route, but got the same result. question. how do you have mathematical functions into the if statement function. in your example
If Column 1 = PM
then, time1 + 12
else, time 1

In this case, time1 would be the number associated with the column with AM/PM…

EX
time1 column1
6 PM

appreciate your help thus far. I always try to search things before posting, but cant always find specific examples. if you can do math with an if statement, that would be helpful as a tip in the if statement ? area

7

You can do something like this:

image
image691×748 37.7 KB

Then use Calculate to get a new 24 hour value by adding hours and add columns.

8

ahhh. yes. nice. what about turning negative numbers positive?

there will be may of these where the end time is 6 and the beginning time is 18 and the number will be -12, which is a 12 hour shift. any way to convert neg to pos?

9

never mind. I was overthinking it. I just extracted the last two digits. but in the future, is there an “absolute value” conversion.

10

There is an abs fn in Calculate. But you might be better off using modulus.