working with cells which contain general formats

teamsimpson

New Member
Joined
Sep 20, 2002
Messages
22
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
Mar 2, 2002
Messages
1,936
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

New Member
Joined
Sep 20, 2002
Messages
22

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Sep 20, 2002
Messages
22

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is a custom function which will convert your text times to Excel times:

Code:
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

New Member
Joined
Sep 20, 2002
Messages
22
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.
 

Forum statistics

Threads
1,147,450
Messages
5,741,189
Members
423,647
Latest member
lyanndominique

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top