Well, this is not as simple as you thought it might be. But you probably figured that out, since your question remained unanswered for 2.5 weeks...
The first challenge is that you need a date "scaffold," so that you can get values for cash requirements in increments that do not correspond to the lines in your data. So, I created a table in Excel, with all dates between 1/1/2016 and 9/26/2018 (a nice round number of 1000 days).
The next challenge is joining that date scaffold with your data. The biggest problem here is that neither data blending nor a standard Excel table join allows a date join on >= or <=, which is what you need because of [Start Date] and [Finish Date].
What I had to do is create Custom SQL. In order to do that against Excel, I had to use a "legacy connection." You can search for details about that, but it comes down to opening the Excel file by clicking the down-arrow on the Open button, and choosing "open with legacy connection." The Custom SQL is tricky too, since it is a little different than SQL Server. I settled on:
select Date, [Start Date], [Finish Date], [Tactic], [Duration], [Labour], [Expense], [Total], [Hours]
from [date scaffold$]
left outer join [data$] on ([date scaffold$].Date >= [data$].[Start Date]
[date scaffold$].Date <= [data$].[Finish Date])
The rest is more straightforward. I created a calculation of the number of days in each line (noting that the days are inclusive, so +1 is needed). Then dividing the total for the line by the number of days to get the daily amount for the line.
So, using Date for the timeline on columns, and the sum of the amount per day for rows, we can get the amount of cash required at whatever frequency selected for Date. See the attached example for details.
Does that help?
Budget Sample.twbx 135.0 KB
Hey Bruce, Bill -
Did you ever work through this? I'm working on a similar resource spread and would be interested to see your attached example, Bill, but it doesn't seem to be there. The idea of the scaffold and greater/lesser than join is great, though, thanks!
Do you still have the example?
That's interesting, I wonder what happened to the attachment. Perhaps I forgot to attach it. If so, I'm sorry to be 6 months late in doing so. Fortunately, I still had the file, and I have now attached it to my original answer. I hope that answers your questions.
Thanks a lot Bill, that's great stuff.