Time Formatting

[Guest]

I have a value in a spreadsheet supplied to me that reads as:
"10:08:40, 25/08/2007"
Using the LEFT function I have pulled out "10:08:40" successfully but cannot
seem format it as Time no matter what I do. I have used the Format function
and the Format Painter. Can anyone help?

 

[Guest]

I have a value in a spreadsheet supplied to me that reads as:
"10:08:40, 25/08/2007"
Using the LEFT function I have pulled out "10:08:40" successfully but cannot
seem format it as Time no matter what I do. I have used the Format function
and the Format Painter. Can anyone help?

Its OK, I have answered my own question. There may be a better way of doing
this but I have resolved the problem by copying the data into an Access dbase
table with a single field pre-formatted as Date/Time. I then copied that data
back into Excel - hey presto - Time Format!
There may be a better/quicker way of doing this. If so I'm still interested
but at least I can carry on with my work now.

 

[MartinW]

Hi Fat Bloke,

If you extract a text value from a text value such as you explain
it will remain a text value. To convert a text value to a number value
it is simply a matter of multiplying by 1, or adding zero or using
other tricks in your formula. The way you convert these values
alters with the situation and what you are trying to achieve.

In this situation I imagine you used something like
=LEFT(A1,8)
which will return a text value

=LEFT(A1,8)*1
=LEFT(A1,8)+0
=--LEFT(A1,8)
will all return a number value

HTH
Martin

 

[Dave Peterson]

If your data is in a single column, you could use Data|Text to columns.

Choose delimited by comma and space
And choose General for the first field (the time)
and choose date (dmy) for the second field

I'd insert a couple columns to the right so that I didn't overwrite any existing
data.

And if you have multiple columns like this, you can do each column one at a
time.

 

[Ron Rosenfeld]

On Sun, 2 Sep 2007 02:34:00 -0700, Fat Bloke <Fat

I have a value in a spreadsheet supplied to me that reads as:
"10:08:40, 25/08/2007"
Using the LEFT function I have pulled out "10:08:40" successfully but cannot
seem format it as Time no matter what I do. I have used the Format function
and the Format Painter. Can anyone help?

The problem is that the value you pulled using the LEFT function is TEXT, and
for Excel to interpret it as TIME, and be able to format it, you must convert
this value to a numeric value that Excel will properly interpret.

The simplest way is to prefix your LEFT(... with a double unary.

e.g. =--LEFT(A1,8)

You will then get a value like 0.422685 which you can then format as TIME.
--ron