Frequency with dates, and vlookup combination

[wynes23]

as you can see in the attached excel.
Goal:
in Business Calculator, in B20 cell, everytime when select an expense, if the date on row 3 is within 7 days of the frequency identified in Business Expenses tab, then populate the expenses under each date on row 20.

it is so hard for me as there is different frequency of dates. each expense could have 4 different payment dates .. it is just so hard for me. I tried few hours now.
please help.

I uploaded to googlesheet below. thank you in advance.

Excel Question

Account Balances

docs.google.com

 

[awoohaw]

How do you determine the month due for each quarter? for each week?
The month is easy since it is every month at the top of your worksheet.
The year, likewise is easy, since the month can be grabbed from the lookup table.
However, you only have one month for the quarter, so, how do you choose the other 3, or is the quarter number listed the month end of the first quarter (so these numbers should be 1,2, or 3 and I see some 4's)?
How will you work the week? Are you using weeknumber of the year? and do you wan the due date to be a specific day of the week (Friday, for instance)?

 

[wynes23]

Couple images to show you some info. Sorry for the hand writing.

coul

How do you determine the month due for each quarter? for each week?
The month is easy since it is every month at the top of your worksheet.
The year, likewise is easy, since the month can be grabbed from the lookup table.
However, you only have one month for the quarter, so, how do you choose the other 3, or is the quarter number listed the month end of the first quarter (so these numbers should be 1,2, or 3 and I see some 4's)?
How will you work the week? Are you using weeknumber of the year? and do you wan the due date to be a specific day of the week (Friday, for instance)?

Hello Awoohaw,
thanks for looking into this problem. If it is Quarterly, the month needs to add 3 months to it. such as August -> November -> Feb
if it is weekly, then Vlookup need to populate them in the tab every time "search for it. Due day for each week could be Monday.
thanks!!

 

[awoohaw]

@wynes23 , in the example above you have the quarterly month as 4... So, I get that if the column month is 4 that you would need to compare the days. But, how do you know what to do in the 7th month, which should be a quarter? or more difficult, the 1st month which is a quarter but they are alway comparing to the 4? There is probably some tricky way to use arithmetic with column numbers to come up with something, but, I want a clearer definition. specifically what does the month number of 4 mean with respect to the 4 quarters of a year?

For weeks, you're saying to ignore the calculation and always bill, is that correct?

 

[wynes23]

@wynes23 , in the example above you have the quarterly month as 4... So, I get that if the column month is 4 that you would need to compare the days. But, how do you know what to do in the 7th month, which should be a quarter? or more difficult, the 1st month which is a quarter but they are alway comparing to the 4? There is probably some tricky way to use arithmetic with column numbers to come up with something, but, I want a clearer definition. specifically what does the month number of 4 mean with respect to the 4 quarters of a year?

For weeks, you're saying to ignore the calculation and always bill, is that correct?

hi Awoohaw, thanks for the additional questions. if this helps, consider the month under quarterly being the start of a quarterly payment. for example, if starts in April, then next is July, then Oct. then Jan 2024. Some of the expense could very well be new entry. Does this make sense? thank you for asking.

 

[awoohaw]

@wynes23 , okay. I just think it would be cleaner to have a consistent month modulus (1,2,3) to start with. I just got drawn into something, and I may not be able to look at this for a few days. I hope the questions I've asked and your answers help other folks in the forum come up with a solution before I can get back.

 

[wynes23]

@wynes23 , okay. I just think it would be cleaner to have a consistent month modulus (1,2,3) to start with. I just got drawn into something, and I may not be able to look at this for a few days. I hope the questions I've asked and your answers help other folks in the forum come up with a solution before I can get back.

Thank you. If this is solved elsewhere. i will also update.
than you for your time.

 

[awoohaw]

Thank you. If this is solved elsewhere. i will also update.
than you for your time.

I hope you haven't posted this in other forums. As that may be against forum rules, please review them.

 

[wynes23]

I hope you haven't posted this in other forums. As that may be against forum rules, please review them.

oh. thanks for reminding me. No. I did not mean to post anything. I plan to ask around on my in-office days next week. obey rules is a must.

 

[wynes23]

Figured out myself. please close this question. thank you.