# Formula for contract values based on start date pro rata

#### natasha_tracy

##### New Member
Hello,

This forum is great.

I have a complicated question (or one that confuses me anyway) -

I have been set a task to calculate revenue for different quarters for different business won. So have an annual value and I need to calculate how much of that value falls in each quarter (1, 2, 3, 4), but I also need to take into consideration the start date of the contract (dd/mm/yyyy). So if the contract starts on 27th november 2007, then there's no data in Q1, Q2 or Q3 and I need to pro rata the value for Q4 - so only from the 24th Nov. It would be simple if all the contracts started on 1st Jan, because I would just divide them by 4, but they don't.

I don't even know where to start and I have rows and rows and don't want to do individual formulas for each contract. Can anyone help? I hope this makes sense.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### natasha_tracy

##### New Member
I've been thinking about it and I imagine I will need an IF statment - but can't work out how to write it... in English though, I guess I need (for Q1)

IF Contract Start Date is greater than 31/03/2007 then \$0 Else, calculate the number of days from contract start date to 31/12/07 (31st Dec) and divide the Annual Value by the number of days, then multiply that value by the number of days from Start Date to 31/03/2007 (31 March).

Does this look right? And if so, how do I write it in'Excel speak' (forumla) -or do I need to add in an extra column to calculate the days?

#### eliW

##### Well-known Member
Hi and welcome to the board!

How is your data arranged ? and what do you want as a result ?

Say for business A annual revenue was 1000\$ start day 1 Jan = then what?
do you want Q1=250 Q2=250 Q3=250 Q4=250 ???
Business B also 1000\$ but started in 1 Jul
do yo want Q1 =0 Q2=0 Q3=500 Q4=500 ???
and so on?

BTW the best way to use quarters is by Pivot Table

#### natasha_tracy

##### New Member
Hi and welcome to the board!

How is your data arranged ? and what do you want as a result ?

Say for business A annual revenue was 1000\$ start day 1 Jan = then what?
do you want Q1=250 Q2=250 Q3=250 Q4=250 ???
Business B also 1000\$ but started in 1 Jul
do yo want Q1 =0 Q2=0 Q3=500 Q4=500 ???
and so on?

BTW the best way to use quarters is by Pivot Table

Sorry - I thought I was being obsure - couldn't work out how to explain it - yes that's exactly what I want.

Replies
1
Views
640
Replies
0
Views
196
Replies
2
Views
293
Replies
0
Views
107
Replies
5
Views
207

1,191,225
Messages
5,985,366
Members
439,960
Latest member
Musa_dabban

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back