Distribute the Number 1 between two Dates

[Jaberwokie]

I am looking for the formulae that does the following spread / distribution between a start date and an end date: if the start date is mid month it recognises the value to not be 1, then between the start and end months inserts the value 1 and for the final month identifies the value of the end date. e.g. refer to the table below.

Description

Net Programme

Oct-19

Nov-19

Dec-19

Jan-20

Feb-20

Mar-20

Apr-20

May-20

Jun-20

Jul-20

Aug-20

Sep-20

Oct-20

Nov-20

Dec-20

Start Date

End Date

2019

2020

Q 4

Q 1

Q 2

Q 3

Q 4

Overall Project

Contract Award -> Practical Completion

14/10/2019

10/12/2020

0.5

1.0

1.0

1.0

1.0

1.0

1.0

1.0

1.0

1.0

1.0

1.0

1.0

1.0

0.4

<tbody>
</tbody>

 

[Gerald Higgins]

Hi, welcome to the board.

Why are the results 0.5 for the start month, and 0.4 for the end month ?

I'm guessing the start month is 0.5 because starting on 14th October means there are 18 days in October where the contract is live, and 18 days live over 31 days in October = 0.58, and you're choosing to round DOWN for the start month.
I'm guessing the end month is 0.4 because ending on 10th December means there are 10 days in December where the contract is live, and 10 days live over 31 days in December = 0.323, and you're choosing to round UP for the end month.

Is this right ?
If yes, it should be straightforward to write a formula to do this, but I'll wait until you confirm.

Comment - it's usually best to explain this kind of thing in the OP, rather than expecting people to guess

 

[Peter_SSs]

Welcome to the MrExcel board!

How did you (manually) calculate the 0.5 and the 0.4?

Edit: Ah, hadn't seen Gerald's post.

 

[Jaberwokie]

Hi, welcome to the board.

Why are the results 0.5 for the start month, and 0.4 for the end month ?

I'm guessing the start month is 0.5 because starting on 14th October means there are 18 days in October where the contract is live, and 18 days live over 31 days in October = 0.58, and you're choosing to round DOWN for the start month.
I'm guessing the end month is 0.4 because ending on 10th December means there are 10 days in December where the contract is live, and 10 days live over 31 days in December = 0.323, and you're choosing to round UP for the end month.

Is this right ?
If yes, it should be straightforward to write a formula to do this, but I'll wait until you confirm.

Comment - it's usually best to explain this kind of thing in the OP, rather than expecting people to guess

That is correct, it was more to demonstrate the the beginning of the period and the end were not to equal 1.

 

[Jaberwokie]

That is correct, it was more to demonstrate the the beginning of the period and the end were not to equal 1.

 

[Gerald Higgins]

OK here goes.

Let's make some assumptions.
1) Your START DATE is held in cell B4.
2) Your END DATE is held in cell C4.
3) The month dates are held in row 1, with the first one, Oct 19, in cell D1.
4) The month dates are actually held as the date of the first day of the month (e.g. 1st Oct 2019), and then have a number format applied so that they only show the month and year. They are real Excel dates, not just text strings. This is important !

In cell D4

=IF(AND($B4<=D1,$C4>=E1),1,IF(AND($C4>=E1,$B4>=D1),ROUNDDOWN((E1-$B4-1)/(E1-D1),1),IF(AND($B4< D1,$C4> D1,$C4< E1),ROUNDUP(($C4-D1+1)/(E1-D1),1),0)))<d1,$c4><e1),roundup(($c4-d1+1) (e1-d1),1),0)))

and copy across as far as required.

COMMENT
This won't deal correctly with projects that start and end in the same month.
If you need something that does this, post back.

This solution also might throw in odd results for the very last month on your calendar, but there are workarounds for that.

I've had to add some space characters in the formula, after < and > symbols, to deal with a known bug on this board.
Make sure you have no space characters in your final formula.
</e1),roundup(($c4-d1+1)></d1,$c4>