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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies