12 AM Time Conversion

[Miglani]

Hi all,
I have two columns in my data.

Structure:

What I want to achieve: Date time difference between these 2 columns.

What I'm doing: 

1. Convert Column2 to time format use "hh:mm" in datetime tool.

2. Formula Tool: If column2 = "Midnight" then "00:00:00" else column2 endif

2. Do datetimediff(column1,column2,"minutes")

What's going wrong:

3rd row is returning 1429(It's taking 12 AM of next to next day) when it should return 11.

Why is this happening and what can I do to make this work?

Thanks.

[apathetichell]

Yes - there's  a sign difference but that's all there is. Look at it this way - the time from midnight to 8 a.m. is not the - (the difference from 8 a.m. to midnight). In time the difference from 0 to 8 is the - of the difference from 8 to 0. Obviously in date the difference in one is 8 hours and the other is 16 hours and my illustration is to show you that that's not how Time works.

I don't think your midnight solution will adequately solve problems unless that is the only situation wherein the later time (i.e. higher number) will be lower than the first number. Look at it this way (assume that these are login times) - if someone logs in at 22 and logs out at 1 am we know that's 3 hours - but TIME will view that as 21 or -21 depending upon which field is first in your formula.

[Miglani]

@apathetichell  I agree with you. In my case, one row is for "one date".

So if there's a case where it's 00:02 to 23:49 that's a lot of minutes.

In case where it's 00:00 to 00:02 that's two minutes.

This seems to be working. However, I'm still looking to find some fault in this. Let me know if you come across a situation where this formula(-1440) I've put won't work.