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

Formula That Averages Totals From Different Months Out Of The Year

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

Formula To Convert Months Into Weeks

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

Convert Fractions To Decimals And Decimal To Fraction

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

Convert Fraction From ComboBox To Decimal In TextBox

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

Convert Date From Any Year To Current Year

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

How To List Next 2 Months Of Year

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

Listbox For Months Of The Year

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

Return 12 Months From Year End Date?

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

Add Up Times Worked For 12 Months Of The Year

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

Calculate Number Of Months In A Year

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

Sorting Months By Calendar Year

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

Loading A Combo Box With The Months Of The Year

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

Get Month & Year X Months From Current Date

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

Fraction In Formula Bar

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

Calculate Number Of Months In Specific Year Between Two Dates?

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

Number Of Months In Year 2007 From Date Range

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

Convert Months To Seasons?

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

Formula To Compare Previous Year To Current Year

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

Convert Date To Year/week Of Year/day Of Week

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

Convert Number To Month And Year?

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

Convert Date To Half Year

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

How To Use Formula For Months

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

Formula For 6 Months From A Certain Date

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

Conditional Format - Dates Greater Than 18 Months And 24 Months?

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

Formula For Months To Years Conversion

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

Formula To Display Same Date For All The Months

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

Months Formula - Remaining In Quarters

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

Formula: Use Numbers For Weeks & Months

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

Elapsed Months (many Months Have Gone By Since Todays Date)

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







Copyrights 2005-15 www.BigResource.com, All rights reserved