Convert decimal to months

**djseph** · 01-28-2012, 05:41 AM

Hello,

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.

**dilipandey** · 01-28-2012, 12:32 PM

Hi djseph,

I think a sample file will help in better understanding of the issue. Alternatively, try googling DateDif function which may give you some hint. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**shg** · 01-28-2012, 12:43 PM

Another way, perhaps:

Please Login or Register  to view this content.

**mahju** · 01-28-2012, 12:56 PM

Hi

If A1 has decimal years or decimal months e.g 4.35 then use

Decimal Years to months

Please Login or Register  to view this content.

Decimal months to days

Please Login or Register  to view this content.

**shg** · 01-28-2012, 01:31 PM

djseph,

Please take a few minutes to read the forum rules about cross-posting.

Thanks.

**djseph** · 02-02-2012, 10:51 PM

Sorry shg. I failed to read the rules. I asked to delete the cross post.

Here is an actual inmates situation:

preR.jpg

B8 contains =DATE(YEAR(B3)-B6,MONTH(B3)-C6,DAY(B3)-D6)
This gives me the right date if I manually divide the minimum sentence. I want to enter the minimum sentence in b5, c5, and d5 and have it divided by half for me. However the latter formula will not calculate .5 or 1.5 years. It rounds up thus giving me the wrong date.

**dilipandey** · 02-02-2012, 11:39 PM

Hi djseph,

I would suggest you to work on number of days i.e., 1 year 10 months and 15 days = 680 days (approx.).
So after serving this much term, pre release can be initiated. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**mahju** · 02-03-2012, 08:48 AM

Hi
Based on the year length=365 days and month length=30 days

Please check the attached.

If it is not helpful please upload the sample file (Not image)

Regards

**djseph** · 02-03-2012, 10:59 PM

Mahju,

Your a genius. I would have never come up with that myself. You are definitely 100% above my ability. For all those that have replied thank you very much. I wish I had half of your talent.

**mahju** · 02-04-2012, 02:13 AM

You are welcome

Please mark the thread as solved so other may not waist time on openining and seeing this thread.

Forum Rule:
If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

Thanks and regards