# Formula To Convert Fraction Of Year To Months?

Jan 28, 2012
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.

View 5 Replies
ADVERTISEMENT
Jan 1, 2007

I need is a formula that averages totals from different months out of the year. I already have a yearly average. That was easy. But what I need is a 3 & 6 month average.

I also need it to be most current, so when I am in September, it will take the 3 previous months and average them and same with the 6 month. Then when I move into October, it would take its 3 previous months, i.e. - July,August, September.

View 9 Replies
View Related
Aug 13, 2008

I'm having an issue converting months into weeks. My example is I have people who must provide leaving notice (could be months or weeks). For instance column B details months, and column C details weeks. I'd like to convert months (column B) into weeks in column D, but am not sure of the components of such a formula so I won't list or guess as I'm just not sure.

View 5 Replies
View Related
Feb 6, 2009

I know that if I enter =3/4 in a cell it will give me .75 but is there is formula to convert fractions into decimals or is this the best way to do it? I was also wondering if there is some way to convert the decimal amount to a fraction

for measurement conversions I know that

=B3*25.4 will convert a decimal to mm and so will the formula =CONVERT(E4,"in","mm")

and to convert mm to a decimal a few formula that work are

=CONVERT(H3,"mm","in")

=MROUND(H4/25.4, 1/8)

=H5/25.4

=INT(H6/25.39999918*8)/8

But is there a way to convert a fraction to the decimal and is there a way to convert mm to a fraction instead of the decimal equivalent of the fraction?

or maybe a formula to convert mm to inches but instead of the result being in decimal format it would be in fraction format?

View 4 Replies
View Related
Dec 16, 2007

i have a combo boxes populated with fractions 1/16, 1/8, 3/16, 1/4 etc up to 15/16.

i just want to output the chosen fraction to a textbox as a decimal.

This is done in a userform, not in a cell.

View 9 Replies
View Related
Feb 27, 2009

DATE function won't return TODAY()'s year in the "year" slot.

Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?

View 9 Replies
View Related
Apr 23, 2012

In cell A1 I have a drop down list with all the months of the year. What I want to do is if A1 has January selected, B1 becomes February and C1 becomes March. Basically I want the next 2 months.

What happens when December is selected? How would i have January in B1 and February in C1?

View 2 Replies
View Related
Apr 16, 2006

I am creating a budget spreadsheet. I am trying to create a list box where I choose what month I want to start with and the other months follow in each column, depending on what month picked in my list box.

View 8 Replies
View Related
Apr 17, 2014

Someone will enter their financial year end in the worksheet e.g. 31/03/2014 and I then have 12 cells below it called Month 1, Month 2 etc up to Month 12. I need the cell next to Month 1 to calculate what it would be... so for a Year End of 31/03/2014 month 1 would be April and this needs to apply to the 12 months.

View 2 Replies
View Related
Jan 28, 2006

I need to be able to add up times worked for 12 months of the year. For example if one workd 49 hours and 23 minutes the month of Jan and the same for Feb, how do I do this and keep a running sum of them? I can't see to figure out how to format it so that it does not change to time of day since it will be over 24 hours.

View 10 Replies
View Related
Feb 1, 2012

I need to automatically calculate the number of months a deal runs through 2012 dependant on the start and end date.

I have attached a basic spreadsheet. Column C shows the results I would like the formula to calculate.

View 1 Replies
View Related
Jan 15, 2013

I have just finished sorting my anniversary list by Month (in alpha order)

B C D E F

1 Hire Date MonthDay YearLast Name First Name

2 04/04/2011April 042011Emp 1

3 04/09/1996April 091996Emp 2

4 08/02/2012August022012Emp 3

5 08/09/2004August092004Emp 4

6 08/13/2001August132001Emp 5

7 08/16/2010August162010Emp 6

Is there a way to sort by Calendar year? Jan, Feb, Mar, etc.

View 2 Replies
View Related
Apr 13, 2007

to populate a combo box with the months of the year, without referencing an area on a sheet.

Here is the code that works for me:

Private Sub UserForm_Initialize()

Dim i As Long

Dim varMonths As Variant

varMonths = Array("Jan", "Feb", "Mar", _

"Apr", "May", "Jun", "Jul", "Aug", _

"Sep", "Oct", "Nov", "Dec")

For i = LBound(varMonths) To UBound(varMonths)

Me.ComboBox1.AddItem varMonths(i)

Next i

End Sub

The original question and answer can be found here: [url]

I see there's a second reply which looks a little slicker, but the above code is tried and tested by me.

View 6 Replies
View Related
May 3, 2008

I am trying to get mmm-yy date format for the next 2 consecutive months from now. I used the following code for that reason, but I am not getting.

strDate = Format(DateAdd("m", 1, Date), "mmm-yy")

strDate = Format(DateAdd("m", 2, Date), "mmm-yy")

However if I use this, I get the current months date correctly (like May - 08)

strDate = Format(DateAdd("m", 0, Date), "mmm-yy")

View 9 Replies
View Related
Nov 4, 2011

How do I get a fraction to show as a fraction rather than decimal in the formula bar by formatting the cell as text.

For ex:

I have "-3/4" in a cell formated as "TEXT" I expect it to show "-3/4" in formula bar but it shows "-0.75".

View 7 Replies
View Related
May 19, 2014

I am trying to calculate the number of months in a specific year between two dates.

For example.

Start date 01/06/2012

End Date 01/02/2013

Number of months in 2012 = 6

Number of months in 2013 = 2

How can I write a formula to give me the answer of 6 & 2 from the start and finish dates?

View 10 Replies
View Related
Jan 12, 2008

I have a "start date" and an "end date". Is there any way to tell how many months are included in 2007 from those two dates using a formula? For instance, from 01-Feb-04 to 01-Feb-07, there is 1 month in 2007 (January). And likewise, 01-Jan-05 to 01-Jan-08 there is 12 months in 2007.

As noted, I have the start and end dates. I don't have the "how many months in 2007".

Start Date End Date How many months in 2007

01-Feb-0401-Feb-07 1

01-Jan-0301-Jan-06 0

01-Apr-0401-Apr-07 3

01-Feb-0301-Feb-06 0

01-Mar-0401-Mar-07 2

01-Feb-0401-Feb-07 1

01-Dec-0401-Dec-07 11

01-Jan-05 01-Jan-08 12

View 9 Replies
View Related
Nov 13, 2012

I want to compress my months into seasons. To make things easy I'm going to make winter, jan feb and march etc. i have a column with the months and next to it i have numbers. i need to add the numbers into one season. also i need the months in the left to be differentiated by year so fall 2006 is different from fall 2007

View 4 Replies
View Related
Dec 8, 2013

Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.

Month 2012 2013

Jan 300 100

Feb 275 100

Mar 400 150

April 650

May 454

June 800

July 500

Aug 375

Sep 525

Oct. 300

Nov 410

Dec 510

Sample file is attached : Comparison_Years.2011.xls

View 4 Replies
View Related
Apr 6, 2007

Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)

View 9 Replies
View Related
Jun 11, 2014

how can convert number data to Month and year?

I have data is 27.03.2014 and would like to change this value is Mar-2014.

which formula should be work?

View 9 Replies
View Related
Sep 18, 2012

Trying to take for example, 9/25/2012 and write a formula to make the resulting cell read 2H2012. Or 4/15/2012 and have the resulting cell read 1H2012.

View 5 Replies
View Related
Aug 3, 2012

I have the data for month wise in one tab. now i want to pick up the months from dropdown list... in A1 (April) B1(june)

when i m picking the months it is automatically take those 3 months data from the data tab...

View 3 Replies
View Related
Mar 6, 2006

I came up with a formula that i thought should work but apparently not so could someone be kind enough to post a formula that will take the date in one cell and place it in another but showing 6 months later.

View 9 Replies
View Related
Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

View 6 Replies
View Related
Aug 14, 2009

Here is my formula:

=E3*(1+E9/365)^(365*E5)

Cell E5 contains a place for you to put in the number of years you want

I want to modify this formula so that it calculates months instead of years, but still be based of a 365 day calendar year.

View 14 Replies
View Related
Jul 31, 2013

I have a excel file which deals with accounting systems. It has the Loan date in Cell B6 and Number of dues in cell B7. I need the column from B11 : B34 to be filled with due dates for every month. Suppose if the loan date is 12/05/2013 and number of dues is 10 then in B11 I should get 12/06/2013 , B12 it should be 12/07/2013 and B13 should be 12/08/2013 and so on based on the number of dues.

View 3 Replies
View Related
Apr 11, 2014

In Column A, I have the dates listed as such. Common point is that, they are all 1st day of the month.

In Column B, I have Quarters as such.... (Please see below)

Can I put a formula together, to round up how many months left in that particular quarter ? example

Date Q3 2014 Q3 2014 Q1 2015

9/1/2014 1 3 3

Column A

9/1/2014

12/1/2014

6/1/2015

9/1/2014

9/1/2014

6/1/2015

9/1/2014

9/1/2014

...

..

Column

Q2 2014

Q3 2014

Q4 2014

Q1 2015

Q2 2015

...

...

View 9 Replies
View Related
Nov 20, 2006

It's a formula I'm looking for. The best way to describe it is an example.

I have some numerical data on a sheet from cells A2 to A80. This data represents the amount of days each person has spent on holiday. The data will be shown on a graph, but instead of the graph having "1", "2", "3", "4", along the bottom, I'd like it to have "1 week", "2 weeks", "3 weeks", "1 months", "2 months", "3 months".

I -suspect- the formula will involve the " countif" function (or an equivalent) (eg. if the number is less than or equal to 7, add 1 to the "1 week" box, etc.)

View 5 Replies
View Related
Jul 19, 2009

Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.

View 3 Replies
View Related