2

I'm not an Excel expert (not by far) and I've been having a hard time finding a solution for the following problem. I have a table that represents the scheduled day of shift (A) for each employee (B) and I'm trying to come up with a formula whose outcome should be (for each row) the next shift of that employee after a given reference date (F1).

I've tried (found on the web) with LARGE + COUNTIF (formula below) but the result is the same date for each row whereas I'd like to find the "next date" > reference date (F1) for each employee:

enter image description here

In column C is the following formula:

=IFERROR(LARGE($A$2:$A$12;COUNTIF($A$2:$A$12;">"&$F$1));"").

In column D the outcome I'd like to get.

2 Answers 2

2

If you FILTER the list of dates for rows where the Employee column matches the Employee on the current row, and the date in the Shift column is greater than the reference date, you'll get a list of all shifts for that employee where the date is after the reference day. The "next" shift is just the smallest date from that list.

So, you can use this:

=MIN(FILTER([Shift],([Emp]=[@Emp])*([Shift]>$F$2)))

enter image description here

1
  • I think I prefer your solution @FlexYourData. I find it easier to calculate also the last shift for each employee by simply changing the ">" to "=<" (I had some trouble with ZygD's one but it's probably just me :))
    – Pesetas74
    Mar 2, 2021 at 13:15
2

Not sure if this is the best solution, but you can do it using array formula (entered using Ctrl+Shift+Enter, not a regular Enter):

=INDEX($A$2:$A$8,MATCH(1,($B$2:$B$8=B2)*($A$2:$A$8>TODAY()),0))

enter image description here


If you have a reference date in F1:

=INDEX($A$2:$A$8,MATCH(1,($B$2:$B$8=B2)*($A$2:$A$8>$F$1),0))

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .