How to add custom ordering to a column with continuous data

[#1]

Is there a way to add custom ordering to a column of continuous numbers? I have a column that stores the start time for a given work order. The times are stored as numbers in 24 hr format with the leading 0's removed (e.g. 12:05 am = 500; 3:26 am = 32600; 7:14 pm = 191400). Each work order has multiple entries, and I am tabulating the data, grouping by work order, and displaying the minimum begin time as the true start time for the work order. The problem I have is that there are 3 shifts that work orders can be completed on. 1st shift is 7 am to 3 pm, 2nd shift is 3 pm to 11 pm, and third shift is 11 pm to 7 am. When a work order has an entry before midnight and another after midnight, the min function takes the entry from after midnight as the minimum (e.g. if there is a work order with entries at 11:15 pm and 2:00 am, the min will show up as 2:00 am rather than 11:15 pm).

I know I can create a custom ordering through the column info menu, but I need this to be done programmatically. 

I know I can use << Set Property("Value Order", {Custom Order({...}), Common Order(0)}), but this requires that I know the values and enter them in the Custom Order brackets. The values in the data set will be changing week to week so I can't really specify what the order will be exactly, I just know if the value is less than 70000 it needs to be moved after 235959 and then ascend from there. Is there a way to do this more generally?

I thought about adding 86400 (one day in seconds) to values between 0 and 65959, but this will mess up code I have later on that converts the times into real time format

[#2]

I've been playing around with this some more and I realized the Custom Ordering does not change the output of the MIN function, so it looks like I don't even know how to do this manually.

[#3]

Hi @jbakri ,

  Do you know the date associated with the shift and timestamp for the work order? This should help get it down, where you can take a difference from one row to the next. That way, if the same work order has an enter on 2022/08/08 23:15 and the next entry is on 2022/08/09 00:30, then the "date difference" should help you be able to order the table accordingly.

  Can you share a small piece of your data table, it might be easier to try and figure something out knowing what the data structure is like rather than trying to mock up my own data table.

Hope this helps!,

DS

[#4 · Accepted solution]

I really believe that it would go a long way if you would convert your times into JMP time values.  Once that is done, you will have a correct continuous number, that can be displayed in one of a large variety of formats, including a 24 hr clock.  Then it becomes a much simpler effort to deal with shifts etc, since there are a good number of built in functions to work with time intervals, etc.

[#5]

This is essentially what I ended up doing. I moved my time conversion code further up into the script. After everything was converted to a real time it was much easier to deal with. Thank you!

[#6]

Thanks for your reply. What I ended up doing was converting the data to a real time earlier in the script. This allowed me to manipulated it much easier. Thanks!