Column Formula Help - Helper Checkbox for Shift Entries

Brett B.

Hello Community,

I am looking for some help with a helper column checkbox formula needed to support reporting of data to a dashboard and downstream formulas.

I have four shifts (A,B,C,D) entering data points into a sheet, with the potential for multiple line entries occurring per shift per day worked (see example below).

I need the most recent shift's entries and the previous shift's entries to be reported out to a dashboard and used in downstream formulas. To do this, I have created a checkbox helper column (Shift for Reporting) but I am struggling to find a formula that will check the most recent shift's lines, as well as the previous shifts lines and update when lines are entered. I am not sure if it is relevant (likely dependent upon the functions used), but sequencing of shift entries will include all of the following:

A follows B, B follows A, B follows C, C follows B, C follows D, D follows C, or D follows A.

Any help or suggested formulas are greatly appreciated as I have it a wall with this one. Thank you!

Paul Newcome

So you need it to flag rows that are for the last two entries of the last two shifts based on the [Shift Name] column being different?

In that case, we can ignore the [Most Recent Entry] column and instead use a [Start Shift] column with the following column formula:

=MIN(COLLECT([Row ID]:[Row ID], [Shift Name]:[Shift Name], @cell = [Shift Name]@row, Date:Date, @cell = Date@row))

Then in the checkbox column use:

=IF(COUNT(DISTINCT(COLLECT([Start Shift]:[Start Shift], [Row ID]:[Row ID], @cell >= [Row ID]@row))) <= 2, 1)

Melissa Yamada

Hello @Brett B.

A possible solution is adding 2 helper columns:

1. Row ID - auto-number column
2. Most Recent Entry - text/number column

Then:

Step 1: Add most recent entry formula in the Most Recent Entry helper column

=MAX(COLLECT([Row ID]:[Row ID], [Shift Name]:[Shift Name], [Shift Name]@row))

Step 2: Add this formula on your checkbox column:

=IF(OR(MAX(COLLECT([Row ID]:[Row ID], [Shift Name]:[Shift Name], [Shift Name]@row)) = [Row ID]@row, MAX(COLLECT([Row ID]:[Row ID], [Row ID]:[Row ID], @cell < [Most Recent Entry]@row, [Shift Name]:[Shift Name], [Shift Name]@row)) = [Row ID]@row), 1, 0)

Brett B.

Hi Amanda,

Thank you for the suggestion, it has gotten me closer to the desired result!

The formula suggested appears to check the box for each shift's most recent entry (screenshot below). I am hoping to only check the boxes for the two most recent shift's entries (and for the checks to roll as newer entries are added). In the screenshot below, that would be only lines 22-25. Any additional suggestions?

Paul Newcome

Based on your most recent screenshot, it looks like it is working as expected. 13 and 14 are the two most recent for Shift C. 15 and 16 are the two most recent for Shift D. 22 and 23 are the two most recent for Shift A, and 24 and 25 are the two most recent for Shift B.

Or are you intending to only flag if it has today's date?

Brett B.

Hi Paul,

Thank you for reviewing this one.

I am intending to flag only the lines for the most recent 2 shifts that have made entries to utilize in rolling two shift reporting. The previous 2 shifts may, however, span a two day period and the shift sequencing will vary (possible variations are listed in original post).

For example, if shift A works days and shift B works nights, at the start of shift A today (1/10), I would want to check only lines 22-25 in the screenshot below to support parsing for rolling two shift reporting.

At completion of shift A today, lines 31-33 are entered with data from that shift and I now need the checked boxes to shift to lines 24-33 for the two shift rolling report (shift B on 1/9 and shift A on 1/10).

Any suggestions you can provide to accomplish a rolling 2 shift demarcation are greatly appreciated.

Paul Newcome

So you need it to flag rows that are for the last two entries of the last two shifts based on the [Shift Name] column being different?

In that case, we can ignore the [Most Recent Entry] column and instead use a [Start Shift] column with the following column formula:

=MIN(COLLECT([Row ID]:[Row ID], [Shift Name]:[Shift Name], @cell = [Shift Name]@row, Date:Date, @cell = Date@row))

Then in the checkbox column use:

=IF(COUNT(DISTINCT(COLLECT([Start Shift]:[Start Shift], [Row ID]:[Row ID], @cell >= [Row ID]@row))) <= 2, 1)

Brett B.

That is exactly what I needed. Thank you Paul!

Paul Newcome

Happy to help. 👍️