Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Office2000: Conditional format behaves strangely | Excel Discussion (Misc queries) | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) | |||
How to format a date to a different format | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |