Matching day due with bill amount and falling under the appropriate date range. Quite challenging.

scorpiotail

Board Regular
Joined
Oct 21, 2015
Messages
60
Hello. I'm trying to create a chart that automatically updates cells based on multiple criteria. I have been working on it for a week and I'm getting close. I can get some data to cooperate, but I have a few obstacles. I have made a second chart below the main chart that uses the old DATEDIF() function to find the months between the due date and each successive month over a year. Think of it as a perpetual calendar that is always a year stretched out. The said due date that is being used as the source is always going to be represented as the due day of the bill always in the current month. ie. Due on day 5th will show has 5/1, 6/1, etc as each month goes by. The date ranged column will always start on the 1st and have intervals at the 15th and 1st of each month. I need bill due dates to match with the source range and list the due amount in the appropriate cell in the appropriate column. I don't want to make a mess of things like I did on a previous post, so I will only start with the formula that I'm using to show you.

HTML:
=IF(AND(EDATE(C$4,(C44))>=$B7,EDATE(C$4,(C44))<$B8),C$5,"")

This is the first cell of the main table. c$4= day due, c44=the DATEDIF() value of "0" months beween 5/1 and due date of 5/1, $b7= the first in source date range;$b8= 2nd date, and c$5=amount due.

The formula works easily when day due is aligned with the date value of the source date. It starts breaking down when there are different days due to deal with because the DATEDIF() function counts whole months and my formula doesn't know how count days. I realize that I could use "d" with DATEDIF(), but then EDATE won't work correctly...or can it??

All I know is that you guys are the best in the business and I always learn from this place. Please help me figure out what I'm missing. NOTE: I have tried INDEX and MATCH, LOOKUP, VLOOKUP, Nested IF statements... this is causing a source of frustration for me, lol. I do consider myself intermediate experience and knowledge of Excel if it makes things easier for you. Thank you!
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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
Back
Top