topic Re: Similar function same as excel <FLOOR(S2,"3:00")> in Discussions

Similar function same as excel

bhpan — Sat, 10 Jun 2023 23:27:25 GMT

Need help on similar function for grouping date/time on new column same as excel <FLOOR(S2,"3:00")>

Re: Similar function same as excel

txnelson — Fri, 19 Mar 2021 03:43:57 GMT

Here is one way to handle it, assuming that the S2 value is in a column called Column 1 in the JMP Data table

Informat( Short Date( :Column 1 ), "mm/dd/yyyy" )

Here is another

Date MDY( Month( :Column 1 ), Day( :Column 1 ), Year( :Column 1 ) )

Re: Similar function same as excel

Mauro_Gerber — Fri, 19 Mar 2021 08:56:50 GMT

I assume you mean round to the date so you can tabulate over it.

formula: :Column1- Time Of Day( :Column1 )

or: right click on the column header --> New Formula Column --> Date Time --> Date will do the same

Re: Similar function same as excel

Georg — Sat, 20 Mar 2021 18:23:31 GMT

I think you want to floor the time to the same value for each 3 hour range.

This works by

Floor( :date / (3 * 60 * 60) ) * (3 * 60 * 60)

assuming :date is the original column.

First the original date is divided so that 1 is a step of 3 hours (3 hours x 60 min x 60 s, JMP calculates in seconds), than floor is applied, than it is calculated back to the original range.

Re: Similar function same as excel

Mark_Bailey — Mon, 22 Mar 2021 12:30:59 GMT

This choice is perhaps a matter of personal preference, but JMP provides many functions for date and time values, which can present difficult boundary conditions. (For example, think leap years. Not to worry in this case, but a general caution.) So this case might use this function:

Floor( :date / In Hours( 3 ) ) * In Hours( 3 ) )