Thanks:  0
Likes:  0

# Thread: Formula to convert fraction of year to months

1. ## 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. ## 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. ## 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.

4. ## 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:
 A B 1 Jan 28 2015 minimum term release date 2 3 Jan 28 2012 Sentence start 4 5 Jul 29 2013 Pre-release application date

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

Excel tables to the web >> Excel Jeanie HTML 4

6. ## Re: Formula to convert fraction of year to months

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

## User Tag List

#### Posting Permissions

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