working with cells which contain general formats

[teamsimpson]

I have a column of data which looks like times, ie 1'25"45 but when i select format cells, the data is set to a general format,
he information is correct but not in time format. The data comes from another program, i cannot change the output. I need to highlight the cells with the lowest value, but not the cells that have no data in, i have a conditional format that works on numerical cells, but not on this, can anyone help please.

 

[BigC]

You could use Data / Text to Columns to split the downloaded data into three separate columns (you need to do this in two goes as there are two distinct delimiters [viz ' & "]
& then recompute the total time back into a 4th column upon which you apply the conditional formats, etc.

HTH
BigC

 

[DonkeyOte]

the other way would be to compute the minutes yourself from the exported cell - i.e. to compute in seconds you would take

=if(a1="","",(LEFT(A1,FIND("'",A1)-1)*(60*60))+(MID(A1,FIND("'",A1)+1,2)*60)+RIGHT(A1,2))

You could then do a max on this value.

_________________
LASW10
This message was edited by lasw10 on 2002-10-17 04:35

 

[eliW]

On 2002-10-17 03:27, teamsimpson wrote:
I have a column of data which looks like times, ie 1'25"45 but when i select format cells, the data is set to a general format,
he information is correct but not in time format. The data comes from another program, i cannot change the output. I need to highlight the cells with the lowest value, but not the cells that have no data in, i have a conditional format that works on numerical cells, but not on this, can anyone help please.

If your data represenst minutes,seconds,1/100 of second, you can convert it to seconds by the general formula:

=LEFT(A1,FIND("'",A1)-1)*60+MID(A1,FIND("'",A1)+1,FIND("""",A1)-FIND("'",A1)-1)+RIGHT(A1,LEN(A1)-FIND("""",A1))/100

and then find the minimum.

Eli

 

[teamsimpson]

Thanks for the formula, unfortunatly i am not very experienced in macro writing, i tend to use the record function, then edit, cuts and pastes. How would i corectly put your formula in a macro, presumably i had run a record to select the column for example

Sub Macro1()
'
' Macro2 Macro
' Keyboard Shortcut: Ctrl+y
'
Range("C1:C16").Select
End Sub
Here is an example of the column of data
C
Time
1'07"52
52"61
51"84
51"09
53"83
52"75
50"94
51"04
51"13
51"07
51"15
51"21
50"28

 

[DonkeyOte]

this isn't a macro - but simply a cell formula - if your data is in Col A then enter this formula in Col B

 

[teamsimpson]

The top part is a copy of the macro i have started to write, the list below is just to show you the data i am working with.
I want to write a macro which works from a control button, so when the button is pressed it changes the background on the cells containing the minimum value, and ignoring cells with no values.

 

[Andrew Poulsom]

Here is a custom function which will convert your text times to Excel times:

Function ConvTime(T As Variant)
    Dim MinChar As String
    Dim SecChar As String
    Dim MinPos As Integer
    Dim SecPos As Integer
    Dim Mins
    Dim Secs
    MinChar = Chr(39)
    SecChar = Chr(34)
    If TypeName(T) = "Range" Then
        T = T.Text
    End If
    MinPos = InStr(1, T, MinChar)
    If MinPos <> 0 Then
        Mins = Left(T, MinPos - 1)
    End If
    SecPos = InStr(1, T, SecChar)
    If SecPos <> 0 Then
        If MinPos <> 0 Then
            Secs = Replace(Mid(T, MinPos + 1, Len(T) - MinPos), SecChar, ".")
        Else
            Secs = Replace(T, SecChar, ".")
        End If
    End If
    Mins = Mins / 1440
    Secs = Secs / 86400
    ConvTime = Mins + Secs
End Function

Paste it into a general module.

To use it add a column to your worksheet and type:

=ConvTime(A1)

where A1 is the cell containing your text time. Copy down as far as needed and reference the new column in your conditional formatting.

Or post back if you still need a macro solution.

 

[teamsimpson]

Struggled but opened a new routine, pasted your code in, just as described, but when i run it it comes back with an error
Compile error
Sub or Function
not defined,
it blocks out in dark blue the word Replace in the code.
I really wanted a control button to activate a macro, if possible.