Please Help :)

[ricky_]

Hey All

I need an excel formula does the following calculation:


Notification Date Exit Date Cost July18 Aug18 Sept18 Oct18 Nov18 Dec18 Jan19 Feb19 Mar19 Apr19
01st Sept 18 31st March 100k 14.3k $14.k $14.k $14.k $14.k $14.k $14.k

I need to spread he cost between the notification date and exit date from Jan 18 to Dec 20 for a 1000 rows.....the notification date and exit dates vary and would ideally like this to be accruate based on the number of days for example pro rata cost if its mid month.

Hope this makes sense.

Working all weekend to try and get a fix.

Thanks in advance!!

 

[shg]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Start​

Stop​

Amt​

Aug 2018​

Sep 2018​

Oct 2018​

Nov 2018​

Dec 2018​

Jan 2019​

Feb 2019​

Mar 2019​

Apr 2019​

2​

13 Sep 2018​

17 Mar 2019​

100,000.00​

0.00​

9,677.42​

16,666.67​

16,129.03​

16,666.67​

16,666.67​

15,053.76​

9,139.78​

0.00​

In D2:

=MAX(0, MIN($B2, EOMONTH(D$1, 0)) - MAX($A2, D$1) + 1) * $C2 / ($B2 - $A2 + 1)

 

[ricky_]

Thanks very much, I'll give it a go now!!