# Find next date in series

#### ScottUlmer

##### New Member
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.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Rick Rothstein

##### MrExcel MVP
Assuming your rotation dates are in cells A1:A3...

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

#### ScottUlmer

##### New Member
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

##### MrExcel MVP
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

##### New Member
Great thank you so much!

Replies
3
Views
103
Replies
6
Views
401
Replies
6
Views
126
Replies
4
Views
376
Replies
1
Views
54

1,191,717
Messages
5,988,259
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back