Convert text to time format

[maverick_abhi]

Hey,

I am unable to convert a piece of text that i copy paste from an
external source to time format. Suppose I copy, "July 24 2006, 05:31
PM" and paste the same in excel it just assumes that that the entered
data is text and I am unable to format the same. I need to convert this
to the time format for proceeding with certain calculations. PLease
help.


+-------------------------------------------------------------------+
|Filename: TTT.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5111 |
+-------------------------------------------------------------------+

 

[Jon Quixley]

Text and dates are not the same thing. If you want to enter dates and
make them seem as if they are text, you need to do some formatting
first:

There is quite a bit of flexibility built into Excel for this: for
instance you can enter a date like this *24 July 2006 11:45* or *July
22 05:15*, what you need to do is configure the format of the cell so
Excel understands what you are trying to do. Click on the cell and
either press Ctrl+1 together or go to Format/Cells, Select Custom at
the bottom of the list of options and set your configuration; in the
example you had in the attachment, this would be mmmm dd yyyy hh:mm.
There are other ways of doing this, especially in terms of how hours
and minutes are entered, by far the easiest way at the end of the day
is to us the hh:mm configuration that Excel understands immediately
rather than taking the simpler decimal hh.mm option that leads to all
sorts of problems later.

Cheers
Jon

 

[Scoops]

Hey,

I am unable to convert a piece of text that i copy paste from an
external source to time format. Suppose I copy, "July 24 2006, 05:31
PM" and paste the same in excel it just assumes that that the entered
data is text and I am unable to format the same. I need to convert this
to the time format for proceeding with certain calculations. PLease
help.

Hi maverick_abhi

Try this with your pasted value in A1:

=TIMEVALUE(MID(A1,FIND(",",A1)+3,8))

Format the result cell to hh:mm

It requires your pasted values to be the same each time, if it's not it
should help you toward a solution.

Regards

Steve