Hi there
I need a formula that will look up a date within another date range and return the date I'm looking for into another cell.
eg. (See below table) I have 26 fortnightly date ranges in 2 columns (From Date and To Date) - these are date ranges to calculate fortnightly payment amounts to students. I then have one date range in 2 cells (From and To) where the student is on leave. The leave dates can fall at any time over the fortnightly date ranges. I need to:
- Find the first date of the leave period within the payment date ranges and return it to the corresponding row of the 'Leave from' column.
- Find the last date of the leave period found in within the payment date ranges and return it to the corresponding row of the 'Leave to' column.
| Stipend | Payment from | Payment to | Stipend amount | Leave from (inc) | Leave to (inc) | Annual amount |
| | | | | 18/01/2019 | 15/02/2019 | 27596.00 |
| 1 | 1/01/2019 | 14/01/2019 | 1061.38 | | | |
| 2 | 15/01/2019 | 28/01/2019 | 1061.38 | | | |
| 3 | 29/01/2019 | 11/02/2019 | 1061.38 | | | |
| 4 | 12/02/2019 | 25/02/2019 | 1061.38 | | | |
Thanks very much
Steph
