How do I create a formula column with custom dates/shift?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?
AnalyticsData TableOtherScriptingRe: How do I create a formula column with custom dates/shift?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?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?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"
);
