Microsoft Excel - how to calculate years and months.
Asked By creation kumari
22-Jun-11 12:04 PM
Hi,
I have two tables: Table # 1 and Table# 2
Question: In table #1 I have calculated the
Total years as
7 years 0 months
I would like to apply formula in the Table #2 to calculate total years and months. Which formula to use? Please help1
|
TABLE No 1
|
Total months
|
|
Table No 2
|
Total months split into total years and total months
|
|
|
0
|
months
|
|
|
0 years 0 months
|
|
|
|
66
|
months
|
|
|
5 years 6 months
|
|
|
|
15
|
months
|
|
|
1 years 3 months
|
|
|
|
3
|
months
|
|
|
0 years 3 months
|
|
|
|
0
|
months
|
|
|
0 years 0 months
|
|
|
Total
|
84
|
months
|
|
TOTAL years and months
|
|
|
Total years
|
7 years 0 months
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
pete rainbow replied to creation kumari
how about, you'll need to add a ref to reg exp
used reg exp which maybe over the top if your string are always fixed with spaces, you could probably just use split instead
Function YearsMonths(range As range)
Dim totalYears As Integer
Dim totalMonths As Integer
totalYears = 0
totalMonths = 0
For Each c In range.Cells
totalYears = totalYears + CInt(RegExecute(c.Value, "\d{1,}"))
totalMonths = totalMonths + CInt(RegExecute(RegExecute(c.Value, "\d{1,}\s{1,}\w{1,}$"), "\d{1,}"))
Next
YearsMonths = CInt(totalYears + totalMonths / 12) & " years " & (totalMonths Mod 12) & " months"
End Function
Function RegExecute(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
Dim r As New VBScript_RegExp_55.RegExp
r.Pattern = Pattern
r.IgnoreCase = IgnoreCase
If r.Test(Value) Then
Dim allMatches As MatchCollection
Set allMatches = r.Execute(Value)
RegExecute = allMatches(0)
Else
RegExecute = ""
End If
End Function
pete rainbow replied to creation kumari
if single space between bits then this'll do...
Function YearsMonths(range As range)
Dim totalYears As Integer
Dim totalMonths As Integer
Dim bits As Variant
totalYears = 0
totalMonths = 0
For Each c In range.Cells
bits = Split(c, " ")
totalYears = totalYears + CInt(bits(0))
totalMonths = totalMonths + CInt(bits(2))
Next
YearsMonths = CInt(totalYears + totalMonths / 12) & " years " & (totalMonths Mod 12) & " months"
End Function
Jackpot . replied to creation kumari
Could you please let us know how your table 2 is..Do you have years and months in subsequent rows..Is the numerics seperated from the text...etc;
Separate colum Excel Hi Can anyone help me with a formula for Excel 2007 to separate the colum in to multiple colum ? below the examples and i need Suite 150 Birmingham AL 35244 2224 Cahaba Valley Drive Suite A-1 Birmingham AL 35242 Excel Worksheet Discussions Microsoft Excel (1) Excel 2007 (1) Macro (1) LEFT (1) VBA (1) VBScript (1) VbTextCompare (1) Database (1) This but city names with 2 or more words will not work using that logic. - - Biff Microsoft Excel MVP The problem is that there are no reliable separators between the end of the
Displaying Actual Cell Values Excel This is a screenshot, it may explain more what I am trying to do: http CELL references, I would like to display the actual values. Is this possible? Thanks, Ty Excel Discussions ActiveCell.DirectPrecedents.Areas (1) ActiveCell.Comment.Delete (1) Microsoft Excel (1) ActiveCell.AddComment (1) Excel 2003 (1) Excel 2007 (1) Error (1) Areas (1) Perhaps something like this In C1: = " = "&A1&"+"&B1 - - Max when you are done so that you do not destroy the links. HTH, Bernie MS Excel MVP Can a sub do this? Don't think OP wants to do it manually written, it is limited to links on the same sheet, I think HTH, Bernie MS Excel MVP Sub Macro1() Dim myA As Range Dim myC As Range Dim myC2 As Range
CurrencyConverter_Class.aspx using System ; using System . Collections . Generic ; using System . Linq ; using System . Text ; using Excel = Microsoft . Office . Interop . Excel ; using System . IO ; namespace ExchangeCurrencyRate . Parser { class Program { static void Main ( string [] args ) { var result x )) { writer . WriteLine ( "{1}{0}{2}" , System . Globalization . CultureInfo . InvariantCulture . TextInfo . ListSeparator , k , result [ k ]); } } Excel . Application app = new Excel . ApplicationClass (); try { Excel . Workbook book = app . Workbooks . Add ( System . Reflection . Missing . Value ); Excel . Worksheet sheet = ( Excel . Worksheet ) book . Worksheets . Add ( System . Reflection . Missing . Value , System . Reflection . Missing . Value , 1 , Excel . XlSheetType
Get text from Win Clipboard into VBA variable Excel VBA in Excel 2007 I have data on the clipboard of a specific format: Start: (') (915855.639280, 638485 I search groups and whatnot. Can anyone help me with this? - Paul Schrum Raleigh, NC Excel Programming Discussions DataObj.GetFromClipboard (1) DataObj.PutInClipboard (1) MSForms.DataObject (1) DataObj.GetText (1) DataObj.SetText (1) Excel 2007 (1) VBA (1) Debug.Print (1) http: / / www.cpearson.com / excel / clipboard.aspx - - Gary''s Student - gsnu201001 Thanks for your response. When I tried the code defined. In VBA, go to the Tools menu and choose References. There, scroll down to Microsoft Forms 2.0 Object Library and check that entry. Then use code like the following You can find information and code for working with the clipboard at www.cpearson.com / Excel / Clipboard.aspx . The rest of the code is just a careful application of the standard text functions. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] VBA
From word to Excel? Excel Hi I am doing my taxes and need to find a way to convert my stocks transactions during the year) from a text pdf form or Word document into an excel format. Is there a way to do this? I can receive my 1099B data from pdf or as a printed statement. But I need it in the form of a Excel spreadsheet with multiple columns. As a small example (the list is much longer which is the pdf into Word the same data copied and pasted from the pdf to an Excel document. They are at: http: / / www.orthohelp.com / 1099b.doc http: / / www.orthohelp.com / 1099b.xls Is there a way to convert this into an excel spreadsheet format with data columns that I can manipulate? Thanks. Jeff Excel Miscellaneous Discussions Office (1) Excel (1) Word (1) Worksheet (1) Macro (1) VB (1) VBScript (1) NumberFormat (1) Since you