Find next date in series

[ScottUlmer]

I have a data set that could be used for a pivot table. I need to be able to know what the current rotation is. The number of employees and length of time of each rotation is different. For example

today = 12/18/16

The three rotation dates are:
11/1/16
1/9/17
3/6/17

Since today is > 11/1 but less than 1/19 the current rotation would be the 11/1 rotation. However, on 1/9, I need it to know 1/9 is current and 3/6 is next. The issue I am running into is I need a dynamic number employees and can't just say monthly on the same date go to next.

So in theory, I need a formula that is if today > A# check A#+1, repeat until A# >=today.

 

[Rick Rothstein]

Assuming your rotation dates are in cells A1:A3...

=LOOKUP(B1,A$1:A$3)

 

[ScottUlmer]

I do not think I am following you. If I put the date in B1 wich I assume you are wanting me to put =today(), then the calendar will have to have an exact match or else it will not display correctly. Right? Right now I am working on {=indrect(CELL("address",INDEX('Rotation Calendar'!A3:A100000,MATCH(TRUE,'Rotation Calendar'!A3:A100000>Settings!$F$15,0)-1))}. I think that is working... but it seems like a lot of overkill and places for things to go wrong F15 by the way is today's date.

 

[Rick Rothstein]

I do not think I am following you. If I put the date in B1 wich I assume you are wanting me to put =today(), then the calendar will have to have an exact match or else it will not display correctly. Right?

Actually, I used B1 copied down to test the formula; I meant to post it with TODAY() inside...

=LOOKUP(TODAY(),A$1:A$3)

I am not sure what your concern is... the formula will return the dates you indicated you wanted when TODAY() becomes the rotation date.

 

[ScottUlmer]

Great thank you so much!