I'm working on an autopopulating weekly cash flow/forecasting spreadsheet and I need a formula to create entries if a date falls between two others.
For example, the electricity bill is due on the 13th, so if the date in H3 is 3/8/19, and the date in I3 is 3/15/19, the formula in H20 will determine if the due date falls between them and refer to a list of expenses another sheet and return the amount for that expense.
The problem is the due dates are listed as numbers, not dates. I don't know if I need to change the format or find the correct formula or both. I've tried extracting the day from the date in another row and using the median function to see if the due date falls between the two days, now formatted as numbers, but that doesn't always work correctly.
Here's a little more detail on how the sheet is set up:
Row 3 (D3:BC3) contains 52 dates (all Fridays) that autopopulate based on a manual date entry in cell D3.
Column B contains due dates for the expenses that are formatted as general numbers (1, 9, 22, etc.).
Row 7 returns the DAY of the dates from Row 3 =DAY(D3), formatted as general numbers. (I used this because I couldn't figure out how to use the due dates in the formula.)
I've tried using a median function that I cobbled together from trial and error and searching online, but it doesn't work in a few areas, including when the second date is on the first or at the beginning of the next month.
=IF($B$18=MEDIAN($B$18,D7,E7)OR(D7 > E7)AND(E7 > $B$18),Bills.$C$2,"'")
Not sure where to go from here. Probably something needs to be formatted differently or formulas adjusted, but I've reached my limit of knowledge and brain power. Any help is appreciated. Thanks.
For example, the electricity bill is due on the 13th, so if the date in H3 is 3/8/19, and the date in I3 is 3/15/19, the formula in H20 will determine if the due date falls between them and refer to a list of expenses another sheet and return the amount for that expense.
The problem is the due dates are listed as numbers, not dates. I don't know if I need to change the format or find the correct formula or both. I've tried extracting the day from the date in another row and using the median function to see if the due date falls between the two days, now formatted as numbers, but that doesn't always work correctly.
Here's a little more detail on how the sheet is set up:
Row 3 (D3:BC3) contains 52 dates (all Fridays) that autopopulate based on a manual date entry in cell D3.
Column B contains due dates for the expenses that are formatted as general numbers (1, 9, 22, etc.).
Row 7 returns the DAY of the dates from Row 3 =DAY(D3), formatted as general numbers. (I used this because I couldn't figure out how to use the due dates in the formula.)
I've tried using a median function that I cobbled together from trial and error and searching online, but it doesn't work in a few areas, including when the second date is on the first or at the beginning of the next month.
=IF($B$18=MEDIAN($B$18,D7,E7)OR(D7 > E7)AND(E7 > $B$18),Bills.$C$2,"'")
Not sure where to go from here. Probably something needs to be formatted differently or formulas adjusted, but I've reached my limit of knowledge and brain power. Any help is appreciated. Thanks.
