Form Submission Start of Shift

L_123

I have a form for the start of shift for operators. Safety inspection, 3s inspection, and other things they are supposed to do prior to starting their shift.

For the location that I am attempting to implement this solution, we are running a 3 shift model.6am-2pm 2pm-10pm 10pm-6am

I want to show a RYG for each shift if they have submitted said form, Red if they haven't, and green if they have based off of 2 columns, shift, and created.

1st and 2nd shift are fairly easy, as i can just compare the day. 3rd shift however goes across 2 days.

My formula that seems to work sometimes, but pops an error fairly often is below:

=IF(OR(MAX(COLLECT(Created:Created, Shift:Shift, "3rd")) = TODAY(), AND(DATE(YEAR(MAX(COLLECT(Created:Created, Shift:Shift, "3rd"))), MONTH(MAX(COLLECT(Created:Created, Shift:Shift, "3rd"))), VALUE(LEFT(MAX(COLLECT(Created:Created, Shift:Shift, "3rd")), 2))) = TODAY() - 1, VALUE(MID(MAX(COLLECT(Created:Created, Shift:Shift, "3rd")), LEN(MAX(COLLECT(Created:Created, Shift:Shift, "3rd"))) - 6, LEN(MAX(COLLECT(Created:Created, Shift:Shift, "3rd"))) - 15)) >= 10, RIGHT(MAX(COLLECT(Created:Created, Shift:Shift, "3rd")), 2) = "PM")), "Green", "Red")

Broken down:

L3 = largest submission of 3rd shift or MAX(COLLECT(Created:Created, Shift:Shift, "3rd"))

=IF(

OR(

L3 = TODAY(),

AND(

DATE(YEAR(L3), MONTH(L3), VALUE(LEFT(L3, 2))) = TODAY() - 1,

VALUE(MID(L3, LEN(MAX(COLLECT(Created:Created, Shift:Shift, "3rd"))) - 6,

LEN(L3) - 15)) >= 10,

RIGHT(L3, 2) = "PM")),

"Green", "Red")

L_123

Solved. I broke out the third shift only into the sheet summary columns

[3 last h]#

=VALUE(SUBSTITUTE(MID([3 Last Sub]#, FIND(":", [3 Last Sub]#) - 2, 2), " ", "")) + IF(RIGHT([3 Last Sub]#, 2) = "PM", 12, 0)

[3 last day]#

=MAX(COLLECT(Date:Date, Shift:Shift, "3rd"))

[3 last sub]#

=LEFT(MAX(COLLECT(Created:Created, Shift:Shift, "3rd")), 100)

and finally to populate the ryg

=IF(OR(AND([3 Last Day]# = TODAY(), [3 Last H]# >= 20), AND([3 Last Day]# = TODAY(-1), [3 Last H]# < 6)), "Green", "Red")

L_123

Solved. I broke out the third shift only into the sheet summary columns

[3 last h]#

=VALUE(SUBSTITUTE(MID([3 Last Sub]#, FIND(":", [3 Last Sub]#) - 2, 2), " ", "")) + IF(RIGHT([3 Last Sub]#, 2) = "PM", 12, 0)

[3 last day]#

=MAX(COLLECT(Date:Date, Shift:Shift, "3rd"))

[3 last sub]#

=LEFT(MAX(COLLECT(Created:Created, Shift:Shift, "3rd")), 100)

and finally to populate the ryg

=IF(OR(AND([3 Last Day]# = TODAY(), [3 Last H]# >= 20), AND([3 Last Day]# = TODAY(-1), [3 Last H]# < 6)), "Green", "Red")