Results 1 to 6 of 6

Formula to convert fraction of year to months

This is a discussion on Formula to convert fraction of year to months within the Excel Questions forums, part of the Question Forums category; I work in a prison. Inmates can apply for pre-release when they have half their minimum sentence served. For example ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Formula to convert fraction of year to months

    I work in a prison. Inmates can apply for pre-release when they have half their minimum sentence served. For example 2 years 6 months. They would have to serve 1 year 3 months. I use this formula =DATE(YEAR(A1)-A5,MONTH(A1)-B5,DAY(A1)-B6) where A1 is half the years, B5 is half the months, and B6 is half the days. The problem is when an inmate is sentenced to a minimum of 3 years and 3 months. The latter formula will not calculate 1.5 years or 1.5 months. It rounds up. What I would like to do is covert the 1.5 years to months and the 1.5 months to days.

  2. #2
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: Formula to convert fraction of year to months

    What is in cell A1? Their term? Is it a proper Excel date? (if you go to format that cell, what does it say it's formatted as?)

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: Formula to convert fraction of year to months

    A1 is their minimum date such as 1/28/2015. If I could figure out how many months then I could just use =DATE(YEAR(A1),MONTH(A1)-B5,DAY(A1)) where the -B5 would be the months.
    Last edited by Seph; Jan 28th, 2012 at 08:44 AM.

  4. #4
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: Formula to convert fraction of year to months

    Then we also need to know when the sentence started being served.
    With those two bits of data, as long as all dates are proper Excel dates, then halve the difference and add to the start date:
     AB
    1Jan 28 2015minimum term release date
    2  
    3Jan 28 2012Sentence start
    4  
    5Jul 29 2013Pre-release application date

    Spreadsheet Formulas
    CellFormula
    A5=A3+(A1-A3)/2


    Excel tables to the web >> Excel Jeanie HTML 4

  5. #5
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,087

    Default Re: Formula to convert fraction of year to months

    Microsoft MVP - Excel

  6. #6
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: Formula to convert fraction of year to months

    Quote Originally Posted by shg View Post
    Sorry. To focused to get an answer. Failed to read the rules. Can I delete this thread?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com