Home

Look up a date within another date range

StephMills
Occasional Visitor

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.

 

StipendPayment from Payment toStipend amountLeave from (inc)Leave to (inc)Annual amount
    18/01/201915/02/201927596.00
11/01/201914/01/20191061.38   
215/01/201928/01/20191061.38   
329/01/201911/02/20191061.38   
412/02/201925/02/20191061.38   

 

 

Thanks very much

Steph

Related Conversations