Summation format
1. How do i make excel recognise the format i am using?
The Cell: A1=1.12.6 to be recognised as 1minute and 12.6 seconds, divided by 1200, thus A2=0.0605 2. How do i sum the two cell below? A2=1.6L A3=3 (without L) that is in total A4=4.6L. |
1. You would need to use worksheet change events and parse it
Private Sub Worksheet_Change(ByVal Target As Range) Dim part1 As String, part2 As String On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("AA1:AA100")) Is Nothing Then With Target part1 = Left(.Value, InStr(1, .Value, ".") - 1) part2 = Right(.Value, Len(.Value) - InStr(1, .Value, ".")) .Value = (Val(part1) * 60 + Val(part2)) / 1200 End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. 2 =SUMPRODUCT(--(SUBSTITUTE(A2:A3,"L",""))) -- HTH RP (remove nothere from the email address if mailing direct) "J45on" wrote in message ... 1. How do i make excel recognise the format i am using? The Cell: A1=1.12.6 to be recognised as 1minute and 12.6 seconds, divided by 1200, thus A2=0.0605 2. How do i sum the two cell below? A2=1.6L A3=3 (without L) that is in total A4=4.6L. |
All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com