Calculating a value pro rata using dates

**Sai Sari** · 07-31-2010, 05:04 AM

Hi,

Is it possible to use a formula to calculate a cell value based on dates. One fixed, and one variable?

The Problem.

I allocate £100 to cover a policy from 1st Jan to 31 March 2010
On the 15th February, I want to calculate how many days the policy has run, and the pro rata cost of the policy to that date inclusive.

I need to use the formula to calculate thousands of policies. The variable date is the start date, and the fixed date would be say 15th February.

Many thanks in advance....

Sai

**teylyn** · 07-31-2010, 05:05 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Sai Sari** · 07-31-2010, 05:25 AM

Thanks for the quick responce.
How can I upload a spreadsheet to this message?

Thanks

Sai

**teylyn** · 07-31-2010, 05:37 AM

see here http://www.excelforum.com/forum-rule...rum-rules.html

**Sai Sari** · 07-31-2010, 05:40 AM

Policy Cost Effective Date End Date Cut Off Date
£100.00 01/01/10 31/03/10 15/02/10

Required

Number of days used so far inclusive
January 31 days
February 15 Days
Total 46 Days

Cost
100/90*46 £51.11

So, the data results needed are number of days used inclusive
cost of used days so far inclusive

The START date should be variable, and the CUT OFF date, fixed.

Hope this helps, as I do not have the data with me.

**royUK** · 07-31-2010, 06:33 AM

You don't really need your data, some dummy data would do.

How does End Date differ from Cut Off Date?

You have also put your formula in your last post!

**Sai Sari** · 07-31-2010, 03:29 PM

Hi,
Thanks for your reply. I already found a solution that works, and have attached a copy for you to see.
This is for a policy of 365 days.
Thank you all for your help

Sai