Return Value if Date Falls between Two dates

Julie Barbeau

Hi, below is my Pay Period reference sheet. In another sheet, I have a column with one date by task. In this sheet, which formula I can us to find in which period this task happened related to my pay period? By example, I would like to say ''If this task happened on Sunday May 10th , my " Numéro de période" should be 2.

Thanks a lot

Paul Newcome

Try something along the lines of...

=INDEX(COLLECT({Other Sheet Numéro de période}, {Other Sheet Date de debut}, @cell <= [Date Column]@row, {Other Sheet Date de fin}, @cell >= [Date Column]@row), 1)

Julie Barbeau

Hi @Paul Newcome :)

I tryied =INDEX(COLLECT({Numéro de période}; {Date de début}; @cell <= Date16; {Date de fin}; @cell >= Date16); 1) but I received INVALID VALUE error

Paul Newcome

Make sure that all columns housing dates are set to date type columns.

Julie Barbeau

It is.

Julie Barbeau

It works! THANKS!!!! :D

Paul Newcome

Great news! Happy to help! 👍️

J.Barrow

@Paul Newcome

Hey Paul, I know this is a really old conversation, but I hope you respond. how would you add another factor to the formula just in case you had multiple with the same date ranges. like a column in this case with a name?

Paul Newcome

@J.Barrow You would include another range/criteria set inside of the COLLECT function.