topic Re: How do I create a formula column with custom dates/shift? in Discussions

How do I create a formula column with custom dates/shift?

Danial1 — Sat, 10 Jun 2023 23:23:23 GMT

I'm working on an analysis related to working shift strucutre. We operate 12 hours per shift. Meaning we work from 7am-7pm (Day shift, D) and 7pm - 7am (Night shift, N).

Currently I have a formula column script as follows:

"WW" || Char (Week of Year (:date)) || "." || Char (Day of week (:date) - 1) || 
If (Hour (:date) <= 19 & Hour(:date) >= 7, "D", "N")

Seems like it doesnt work the way I expected bcs based on this script, it changed to new day whenever the time reached 12am.

For example:

The script will convert 12/01/2020 5:49:47 AM to WW49.2N. It should be WW49.1N. WW49.2D starts when the date and time is 12/01/2020 7:00:00 AM This is because the time for night shift is from 7pm-7am. Can someone suggest or advise an alternative way to create the script?

Re: How do I create a formula column with custom dates/shift?

txnelson — Tue, 08 Dec 2020 07:22:13 GMT

I think the best way to handle this, is to do a correction on the date time values by subtracting off 7 hours, which makes the shifts line up with the day values

correctedDate = :date - In Hours( 7 );
"WW" || Char( Week Of Year( correctedDate ) ) || "." || Char( Day Of Week( correctedDate ) - 1 ) ||
If( Hour( correctedDate ) >= 0 & Hour( correctedDate ) <= 12,
	"D",
	"N"
);

Re: How do I create a formula column with custom dates/shift?

Danial1 — Tue, 08 Dec 2020 15:15:05 GMT

just wanted to confirm, when you say - In Hours( 7 ) does it mean substrate 7 hours?

Re: How do I create a formula column with custom dates/shift?

txnelson — Tue, 08 Dec 2020 15:33:01 GMT

It subtracts 25200 seconds from the specified value for the column Date for the current row.
25200 = 60 seconds per minute X 60 minutes per hour X 7 hours

Also, I made a slight error in my formula. The If() comparison should have a "<" comparison for the second element, not "<="

correctedDate = :date - In Hours( 7 );
"WW" || Char( Week Of Year( correctedDate ) ) || "." || Char( Day Of Week( correctedDate ) - 1 ) ||
If( Hour( correctedDate ) >= 0 & Hour( correctedDate ) < 12,
	"D",
	"N"
);

Re: How do I create a formula column with custom dates/shift?

Danial1 — Wed, 09 Dec 2020 00:18:09 GMT

I see so jmp would always compute in seconds. btw @txnelson could you explain why we need to substract 7 hours (25,200 seconds) from the actual time?

The scripted condition says any time between 12am - 12pm is Day ("D")

Re: How do I create a formula column with custom dates/shift?

txnelson — Wed, 09 Dec 2020 01:55:35 GMT

Your shifts are from 7am-7pm D, and 7pm-7am N. If a time for the 2nd day night shift is beyond 12 midnight, JMP will return the Day(:date) will return day 3, since JMP measures days based upon midnight. However, if for the calculations, we shift the :date value back by 7 hours, that will mean that the night shift will be from noon to midnight, and never crossing the JMP day boundry.

Re: How do I create a formula column with custom dates/shift?

Ressel — Mon, 30 Jun 2025 12:58:44 GMT

This is interesting, since you could have swapped the positions of the >= and < in the formula:

correctedDate = :date - In Hours( 7 );
"WW" || Char( Week Of Year( correctedDate ) ) || "." || Char( Day Of Week( correctedDate ) - 1 ) ||
If( 0 < Hour( correctedDate ) <= 12,
	"D",
	"N"
);

I am working on a similar problem and the assignment of night vs day shift is slightly different, depending on which relative positions the < and <= symbols have in the if statement. It would be great to allow for direct use of timestamps inside the if formula (if time > 12:00:00 ...). Maybe it is, I haven't researched enough.

However, my main question: How did you decide where to put the < and <= symbols? Naturally, it is important to have an equal division of 12 hours per shift, but this could be achieved with both configurations (< before <=, and <= before <).

Re: How do I create a formula column with custom dates/shift?

txnelson — Mon, 30 Jun 2025 13:52:12 GMT

7PM is the start of the new shift which is hour() 19. Since we are taking a 7 hour offset, the Hour() value that starts the night shift is 19-7 which is 12. If the comparison was <= then a time value of exactly 12 would be considered a day shift.

Re: How do I create a formula column with custom dates/shift?

Ressel — Mon, 30 Jun 2025 14:46:15 GMT

Thanks, I realized this later when tinkering with own file - naturally, after I had posted my not-so-well-thought-through question. Thank you for your patience.