topic Re: How do I round up or round down dates? in Discussions

How do I round up or round down dates?

fionaweston — Sat, 10 Jun 2023 23:24:08 GMT

Hello,

If I have a bunch of Opentimes in the format MMDDYYYY hh:mm and I would like to fix any of these dates that occur between the hours of 1am and 3:59am to "round up" to 4am.

In other words if my Opentime is 10/09/2020 3:12 AM, I need that to round up to 10/09/2020 4:00 AM.

Conversely I have Closetimes in same format and I would like to fix any of these dates that occur between the hours of 1am and 3:59am to "round down" to 1am.

In other words if my Closetimes is 10/09/2020 3:12 AM, I need that to round down to 10/09/2020 1:00 AM.

I use the formula editor in JMP for all my formulae so if I can paste the solution into the formula editor that would be great!

I have attached a sample table.

Thank you for any help with this.

Fiona

Re: How do I round up or round down dates?

Mark_Bailey — Tue, 22 Dec 2020 19:57:34 GMT

This script illustrates the approach:

Names Default to Here( 1 );

date time = 09Oct2020:03:12;

rounded date time = date time - Modulo( date time, In Hours( 1 ) ) + In Hours( 1 );

formatted rounded date time = Format( rounded date time, "m/d/y h:m" );

Show( date time, formatted rounded date time );

The actual formula might be compressed into this expression, assuming that you want a numeric value with the desired Format column attribute:

:date time - Modulo( :date time, In Hours( 1 ) ) + In Hours( 1 )

Note that rounding down to the nearest hour would simply remove the addition of one hour at the end of the expression.

Re: How do I round up or round down dates?

jthi — Tue, 22 Dec 2020 20:03:08 GMT

Most likely there is cleaner/more efficient way to do this but these should work:

Rounded opentime:

If(In Hours(1) <= Time Of Day(:Opentime) < In Hours(4),
	(:Opentime - Time Of Day(:Opentime)) + In Hours(4),
	:Opentime
)

Same idea for rounded closetime:

If(In Hours(1) <= Time Of Day(:Closetime) < In Hours(4),
	(:Closetime - Time Of Day(:Closetime)) + In Hours(1),
	:Closetime
)

Solution built on the formulas "pre-made" by JMP when you right click on column name on date column for Date and Time Of Day:

Re: How do I round up or round down dates?

fionaweston — Tue, 22 Dec 2020 21:45:29 GMT

Thank you so much.

This works!!