Forum Discussion
Fee Projection Formula Based on Dates
- I figured it out mathetes.
Formula is below if you're curious.
Also Retainage is money withheld by one party in a contract to act as security against incomplete or defective works.
=LET(e,EOMONTH(+$N$3:$AP$3,0),(EOMONTH(L4,0)<=e)*IFS(e=EOMONTH(G4,0),D4/K4*E4+D4*F4,e=EOMONTH(I4,0),D4*H4,e<=EOMONTH(L4,J4-1),C4,e<=EOMONTH(L4,J4+K4-1),D4/K4*E4,TRUE,0))
I'm not sure if a formula I'm describing is possible but I thought I'd ask the group for recommendations.
I AM sure that such a formula is possible. (There aren't many, if any, formulas that can't be created, once the specifics have been described clearly. THAT -- getting a clear description -- is where the rub usually enters the picture.)
You could help us help you by posting not just an image but a copy of that actual spreadsheet--put it on OneDrive or GoogleDrive and paste a link here that grants access.
And then there's the matter of the clear description. Looking at the images (granted without studying them closely) it's not clear to me what the different columns are meant to represent, and in particular those off to the right side with various dates as headers. So if you could take some time to explain what those are about, that would help.
The formula will not be the hard part; it's making sure we understand how the parts fit together.