+ Reply to Thread
Results 1 to 6 of 6

Divide a value by months remaining between two dates

  1. #1
    Registered User
    Join Date
    08-07-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    8

    Divide a value by months remaining between two dates

    Greeting's, pardon me for what I'm certain is a minuscule question. I'm not an avid Excel user. I have just started and I mean literally just . I did try searching for this but I got answers (and formula's) that didn't quite fit my query.

    I have a cell where an amount is displayed. I need a formula (or method) to divide this amount by remaining months between today to 28th February, 2016. Mind you, i need the division by month and not days. Goal of this is to find number of day's remaining to achieve an investment value I've set for myself.

    For example: cell has value of 100 (investment target) and i need to find remaining months i have to achieve that value (remaining months will be from today till 28th February, 2016). The remaining months value has to be dynamically updated too.

    Thank you in advance.

    I am using Microsoft Excel 2013.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Divide a value by months remaining between two dates

    The function I recommend is DATEDIF()

    =A1 / DATEDIF(TODAY(), DATEVALUE("Feb 26, 2016"), "m")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-07-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    8

    Talking Re: Divide a value by months remaining between two dates

    Wow.. that's amazing.. I had a friend help me to this - (((YEAR("29-2-16")-YEAR(TODAY()))*12) + (MONTH("29-2-16")-MONTH(TODAY()))+1) :D ..

    Can you help me with reverse of this too?

    For example, I wanted to save - 1200 and till today (from March, 2015 till August, 2015) I've saved only 200. I wan't to know how much I have to save per month till Feb, 2016 to reach my target (that I will get through your formula) and by default, how much should I have saved today, to know how much I'm lagging.
    Last edited by parthmaniar; 08-07-2015 at 01:54 AM. Reason: Start date and end date.

  4. #4
    Registered User
    Join Date
    08-07-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    8

    Talking Re: Divide a value by months remaining between two dates

    For those you are searching for an answer. I found this to be useful:

    (reference cell/(DATEDIF(DATEVALUE("01-03-2015"),DATEVALUE("29-2-2016"),"m")+1))*(DATEDIF(DATEVALUE("01-03-2015"),TODAY(),"m")+1)

    I am at times confused about how many months I need to use, but that's due to my exhaustion and unfamiliarity with excel.

    Thank you!

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Divide a value by months remaining between two dates

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  6. #6
    Registered User
    Join Date
    08-07-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    8

    Re: Divide a value by months remaining between two dates

    Thank you actually

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Divide by remaining weeks between today and September
    By morerockin in forum Excel General
    Replies: 5
    Last Post: 03-30-2015, 11:08 AM
  2. Redistribute Budget Gap Over Remaining Months
    By Horsefly in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-26-2015, 02:26 PM
  3. Taking remaining value and sharing across remaining months?
    By kickme93 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2013, 08:54 AM
  4. Replies: 1
    Last Post: 08-29-2013, 03:17 PM
  5. [SOLVED] Automatically divide cell by days remaining in the week
    By Granny in forum Excel General
    Replies: 4
    Last Post: 09-01-2012, 02:03 AM
  6. Formula to count down months remaining?
    By DownScale in forum Excel General
    Replies: 7
    Last Post: 04-18-2012, 01:17 PM
  7. Months or Years remaining, formatting
    By James C in forum Excel General
    Replies: 3
    Last Post: 09-20-2005, 05:38 PM

Tags for this Thread

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