# Calculating a value pro rata using dates

1. ## Calculating a value pro rata using dates

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.

Sai

2. ## Re: Calculating a value pro rata using dates

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!

3. ## Re: Calculating a value pro rata using dates

Thanks for the quick responce.

Thanks

Sai

5. ## Re: Calculating a value pro rata using dates

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.

6. ## Re: Calculating a value pro rata using dates

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

How does End Date differ from Cut Off Date?

7. ## Re: Calculating a value pro rata using dates

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1