Hi All,
I have a challenge, which I have only partially resolved and need some assistance.
I need the solution to work on Excel 2007 and all subsequent version. not just office 365
I have
Column D - Arrival Date
Column E - Departure Date
Column F - Days (Calculation)
In Field K3 - the Initial Arrival Date
Field L3 - Maximum Time Period (End Date)
Field M3 - Calculation part 1
The aim is to be able to calculate have many days someone has spent between the period L3-L3 Inclusive.
The Calculation in M3 is working for any row where both the Arrival Date and End Date are in the range specified in K3 / L3.
I am struggling with the necessary formula for calculating the net days that only fit within the Range K3 / L3. So Row 6 only 3 days should be calculated and included in the final answer.
Cell H3 shows the calculation I am trying to include for the date ranges which exist outside the criteria or something similar - so The answer I would like the formula to give me back as an answer is in H8.
I have done the basic formulas I am looking at in column I.
I need a formula that sums 3 different situations:
1) Arrival Date Before K3 and Departure Date Less than = L3.
2) Arrival Date Greater than = K3 and Departure Date Less than = L3.
3) Arrival Date greater than = K3 and Departure Date greater than L3.
Hope this is clear.
Many thanks
Bookmarks