Formula support

Regular Visitor

Hi THere 

1 Reply

I'm not sure how you want them 'marked' but let's say we create a column that will have "Shift Con't" to indicate that shift has at least 1 other shift that ended that same day:
=IF(COUNTIFS($A$1:$A$100,$A1,$D$1:$D$100,$B1)-($B1=$D1),"Shift Con't", "")
this counts the number of cases where column A is the same as this row A and column D (end date) is same day as this row's B (start date) and subtracts 1 if this row's start and end date are the same to exclude this row. IF there is at least 1 other row with an end date the same as this row's start date then insert "shift con't" otherwise leave it blank.

BTW I would highly recommend formatting that data as a table (see home tab - format as a table) and then use the column names instead of a range:

=IF(COUNTIFS([Staff],[@Staff],[End date],[@[Start date]])-([@[End date]]=[@[Start date]]), "Shift Con't", "")

In this way the range will always be the size of the table you won't have to make the range extra large to allow for added rows and risk adding "too many" rows and exceeding that range.