# Spread Spend Based Between Dates

#### Roj47

##### Board Regular
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.

Roj

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

### 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...