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.
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!
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: