topic Re: Column formula to round timestamp to nearest minute in Discussions

Column formula to round timestamp to nearest minute

Onjai — Wed, 26 Sep 2018 15:16:51 GMT

Hi,

Looking for a column formula to round timestamp to nearest minute.

27/06/2017 12:21:13 PM need to round to 27/06/2017 12:21:00 PM.

Any suggestions?

Cheers.

Re: Column formula to round timestamp to nearest minute

jules — Wed, 26 Sep 2018 15:37:47 GMT

Hi @Onjai,

I'm sure there are other ways to approach this, but I've had luck with the following when I've needed to do that (this requires that you have a numeric :DateTime column):

Round(:DateTime / 60) * 60

(Edited: replaced with the simpler formula pointed out by Jerry)

Hope this helps!

@jules

Re: Column formula to round timestamp to nearest minute

jerry_cooper — Wed, 26 Sep 2018 15:32:51 GMT

Hi Onjai,
If you're using version 14, you can right-click the heading of your Timestamp column, select New Formula Column->Transform->Round. You'll then get a dialog box to set the desired parameters (because you're wanting to round to the nearest minute, you'll just need to select "Nearest" from the dropdown and click OK). This is the resulting formula:
Round(:Timestamp / 60) * 60

Re: Column formula to round timestamp to nearest minute

Mark_Bailey — Wed, 26 Sep 2018 15:41:57 GMT

:Time Stamp - Modulo( :Time Stamp, 60 );

Re: Column formula to round timestamp to nearest minute

Onjai — Wed, 26 Sep 2018 16:29:09 GMT

Julian,

This worked just fine.

Thank you.

Re: Column formula to round timestamp to nearest minute

Onjai — Wed, 26 Sep 2018 16:31:44 GMT

I am using the latest version of JMP. The transform worked as well.

Thank you!