Formula for Automated allocation of money based on Date

[JayB0730]

Hello,

I have a spreadsheet setup as follows:
Column A: Description of Expense; Column B: Current Hold Amount; Column C: Total Amount Due;

What I want to accomplish is the following:

1. Based on Today(), calculate how many Fridays have passed in the current month (e.g. 1/27/18 = 4; 1/25/18 = 3) & divide that # by Total amount due to determine Current Hold Amount.
2. Also want the calculation to stop after the last Friday in the month until I "reset" the month. This is to ensure if I am not able to record Current Hold Amount in time before next month's first Friday, the calculation will not reset.

Appreciate any help you can provide.

Thank you!
Jay

 

[jorismoerings]

Hi,

The number of fridays past in the month is easily calculated with =NETWORKDAYS.INTL(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY(),"1111101").
However if you want a formula based on Today() you can't make it stop because it will always take the actual date and can't be manually reset.
Are you able to share a mockup of how you want this to work?

 

[JayB0730]

Hi Jorismoerings,

I appreciate you looking into this for me. I mocked up3 different scenarios. Hopefully this will provide more info into what I am looking to do. Let me know if you have further questions. TIA!

Scenario 1: Current Date: Friday, Jan 19, 2018

Results: Based on "Current Date", we are in the month of January and there are only 4 Fridays in this month. Since we have not reached "4" Fridays, the Reset Flag remains at "N". Now the system should calculate that only 3 Fridays have been reached, therefore multiplying the Total Monthly Amount by ".25 x 3 " or ".75" to get "Current Hold" amount.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Total Monthly Amount[/TD]
[TD]Current Hold[/TD]
[TD]Reset Flag[/TD]
[/TR]
[TR]
[TD]Insurance[/TD]
[TD]100[/TD]
[TD]75[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Mortgage[/TD]
[TD]1000[/TD]
[TD]750[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]HOA[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

Scenario 2: Current Date: Thursday, Jan 25, 2018

Results: Based on "Current Date", we are in the month of January and there are only 4 Fridays in this month. Since we have not reached "4" Fridays, the Reset Flag remains at "N". Now the system should calculate that only 3 Fridays have been reached, therefore multiplying the Total Monthly Amount by ".25 x 3 " or ".75" to get "Current Hold" amount.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Total Monthly Amount[/TD]
[TD]Current Hold[/TD]
[TD]Reset Flag[/TD]
[/TR]
[TR]
[TD]Insurance[/TD]
[TD]100[/TD]
[TD]75[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Mortgage[/TD]
[TD]1000[/TD]
[TD]750[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]HOA[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]N

[/TD]
[/TR]
</tbody>[/TABLE]

Scenario 3: Current Date: Friday, Feb 2, 2018

Results: Based on "Current Date", we are now in the Month of February. Since the previous month would have reached 4 Fridays the "Reset Flag" should change to "Y". NOTE: This workbook could be opened at any time so we need the system to know that the flag should have been changed to "Y". With the flag being "Y", the system should only keep the calculation based on January. Therefore the amounts should be multiplied by "1". Once I manually update the flags to "N", the system will recalculate based on the current month of February.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Total Monthly Amount[/TD]
[TD]Current Hold[/TD]
[TD]Reset Flag[/TD]
[/TR]
[TR]
[TD]Insurance[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Mortgage[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]HOA[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
-Jay