+ Reply to Thread
Results 1 to 7 of 7

Calculating a value pro rata using dates

  1. #1
    Registered User
    Join Date
    07-29-2010
    Location
    North West
    MS-Off Ver
    Excel 2003
    Posts
    9

    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.

    Many thanks in advance....

    Sai

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    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. #3
    Registered User
    Join Date
    07-29-2010
    Location
    North West
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Calculating a value pro rata using dates

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

    Thanks

    Sai

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Calculating a value pro rata using dates


  5. #5
    Registered User
    Join Date
    07-29-2010
    Location
    North West
    MS-Off Ver
    Excel 2003
    Posts
    9

    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. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    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?

    You have also put your formula in your last post!
    Attached Files Attached Files
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    07-29-2010
    Location
    North West
    MS-Off Ver
    Excel 2003
    Posts
    9

    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
    Attached Files Attached Files
    Last edited by Sai Sari; 07-31-2010 at 03:35 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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