Formula to return value based on start date and end date?

reacon84

New Member
Joined
Sep 13, 2016
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
Hi all, I'm hoping someone can help me with a spreadsheet I'm trying to build. The top table is where I've got to so far - I have a Start Date, End Date and a Payment amount. I've used =EOM(cell, -1)+1 to convert the Start/End Date into the first day of that month, to correspond with the Jan-20 to Dec-20 headers.

What I'd like to do is have a formula to return the payment amount in the correct cell if it falls within that range (ie the output table at the bottom).

Thanks in advance.

Start DateStart Date MonthEnd DateEnd Date MonthPaymentJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
08/10/202001/10/202008/01/202201/01/2022427
03/10/201801/10/201803/10/202101/10/2021379
29/05/201901/05/201929/05/202201/05/2022800
09/09/201901/09/201909/03/202201/03/2022560
04/10/201801/10/201804/04/202001/04/2020646
15/05/201901/05/201915/10/202001/10/2020262
11/06/201901/06/201911/06/202101/06/2021573
20/01/202001/01/202018/08/202101/08/2021292
16/04/201901/04/201923/04/202001/04/2020542
08/10/202001/10/202008/01/202201/01/2022542
09/11/202001/11/202009/08/202201/08/2022427
Start DateStart Date MonthEnd DateEnd Date MonthPaymentJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
08/10/202001/10/202008/01/202201/01/2022427427427427
03/10/201801/10/201803/10/202101/10/2021379379379379379379379379379379379379379
29/05/201901/05/201929/05/202201/05/2022800800800800800800800800800800800800800
09/09/201901/09/201909/03/202201/03/2022560560560560560560560560560560560560560
04/10/201801/10/201804/04/202001/04/2020646646646646646
15/05/201901/05/201915/10/202001/10/2020262262262262262262262262262262262
11/06/201901/06/201911/06/202101/06/2021573573573573573573573573573573573573573
20/01/202001/01/202018/08/202101/08/2021292292292292292292292292292292292292292
16/04/201901/04/201923/04/202001/04/2020542542542542542
08/10/202001/10/202008/01/202201/01/2022542542542542
09/11/202001/11/202009/08/202201/08/2022427427427
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
Maybe this in F2 copied across and down:

=IF(AND($B2<=F$1,$D2>=F$1),$E2,"")
 
Solution

reacon84

New Member
Joined
Sep 13, 2016
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
Ah excellent! Thank you!

I always think it's going to be more complex than it is - I need to keep it simple!

Thanks again.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Try:
Other.xlsm
ABCDEFGHIJKLMNOPQ
1Start DateStart Date MonthEnd DateEnd Date MonthPaymentJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
208/10/202001/10/202008/01/202201/01/2022427000000000427427427
303/10/201801/10/201803/10/202101/10/2021379379379379379379379379379379379379379
429/05/201901/05/201929/05/202201/05/2022800800800800800800800800800800800800800
509/09/201901/09/201909/03/202201/03/2022560560560560560560560560560560560560560
604/10/201801/10/201804/04/202001/04/202064664664664664600000000
715/05/201901/05/201915/10/202001/10/202026226226226226226226226226226226200
811/06/201901/06/201911/06/202101/06/2021573573573573573573573573573573573573573
920/01/202001/01/202018/08/202101/08/2021292292292292292292292292292292292292292
1016/04/201901/04/201923/04/202001/04/202054254254254254200000000
1108/10/202001/10/202008/01/202201/01/2022542000000000542542542
1209/11/202001/11/202009/08/202201/08/20224270000000000427427
Data
Cell Formulas
RangeFormula
F2:Q12F2=$E2*(F$1=MEDIAN($B2,$D2,F$1))
 

Forum statistics

Threads
1,141,770
Messages
5,708,432
Members
421,568
Latest member
Huxley

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top