Identify Shift from Submission

Neil Watson

I am looking for ideas on how to identify & link a submission using the "Created (Date)" system column. We work shifts either from 07:00 - 19:00 (Day) or 19:00 - 07:00 (Night) and I would like to assign the label "Day" or "Night" to a submission based on the above criteria.

Any thoughts on the best way to approach this?

Kelly Moore

Hey @Neil Watson

You may want to consider a helper column that splits the time portion out of the Created timestamp to simplify the below formula. You may also want a helper Date column using Record a Date to insure that the Date is your local date. The Date portion of the Created timestamp appears as a local date but is stored as UTC(0). When dealing with shifts, you may run into issues if your timezone is not also GMT(0). I'm UTC-6 and the DATE (not the time) changes at 6 or 7pm for me, depending upon daylight savings, as this is when midnight happens in the UTC(0) timezone compared to me. Recording the date using Record Date Automation mitigates this. See here

Here is one approach to indicate the Shift. Most of the formula is stripping the hour value out of the timestamp assumin

=IF(OR(AND(RIGHT(Created@row, 2) = "AM", VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) >= 7, VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) <= 11), AND(RIGHT(Created@row, 2) = "PM", OR(VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) = 12, VALUE(LEFT(REPLACE(Created@row, 1, 9, ""), FIND(":", REPLACE(Created@row, 1, 9, "")) - 1)) < 7))), "Day", "Night")

If you were pulling the time portion out into a separate time helper column, you can use

=IF(LEN(Created@row) = 17, RIGHT(Created@row, 8), RIGHT(Created@row, 7))

then, the Shift formula becomes

=IF(OR(AND(RIGHT([your time helper]@row, 2) = "AM", VALUE(LEFT([your time helper]@row, FIND(":", [your time helper]@row) - 1)) >= 7, VALUE(LEFT([your time helper]@row, FIND(":", [your time helper]@row) - 1)) <= 11), AND(RIGHT([your time helper]@row, 2) = "PM", OR(VALUE(LEFT([your time helper]@row, FIND(":", [your time helper]@row) - 1)) = 12, VALUE(LEFT([your time helper]@row, FIND(":", [your time helper]@row) - 1)) < 7))), "Day", "Night")

Does this help?

Kelly

Neil Watson

@Kelly Moore thanks, the formula works - will test submissions over a 24 hr period just to confirm the transitions at 07:00 and 19:00 are working. Thanks so much!