Spread Spend Based Between Dates

Roj47

Board Regular
Joined
May 4, 2011
Messages
51
Afternoon all and hope I find you well.

I have created a working sheet, but the management and user-friendliness are around 1 out of 10.
I am usually able to get cells to do what I need, but not in a space saving, tidy way and wondered if there is a simple solution to what I try to update quarterly.

---

Column A : A list of activities to do i.e. skim wall, paint wall, fit skirting etc...
Column B&C : Start date and End date
Column D : Networking days (to omit recognised holidays)
Column E : The cost of the activity i.e. skim wall may be £350
Column F : I place upfront costs i.e. plaster for £50

Bit of a jump now.

I have merged each activity cell into 1 cell from 4, so Skim wall referenced is A21:24.

Row 21 : This is the difference between start and end date (B-C)
Row 22 : This returns the start date of the whole project - Activity End date (Set date - C)
Row 23 : =IF(SUM(M21:M22)>-1,IF(M22<=0,1,0),0) Conditional Format to show the cell in Red if returns 1. This creates a bar chart to show The start date and end date.
Row 24: =IF(M15=1,IF($C13=M$5,$I13,($H13-$I13)/($F13-$D13)),0)
This places Column F's figure on the start date and then splits the remaining cost across the rest of the bar chart.

Ie The plaster was £350 across 11 days, so day 1 has £50 in and then £30 is spread across the remaining cells.
If it was 21 days it would be £50 and £15 spread.

This is cumbersome and hard to manage and in addition it is more realistic to have an S-Curve for the spend rather than an equal amount, so rather than the £30 across 10 days it would be more realistic to be 5, 5, 8, 10.... 7, 3, 1.

This is incredibly long winded for which I apologise, and would welcome guidance to a function to save time and how to have the S-Curve linked between 2 dates that move each quarter.

Thanking in advance for your time in reading and all assistance.

Roj
 

Forum statistics

Threads
1,081,626
Messages
5,360,101
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top