Convert text to time format

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.

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

maverick_abhi wrote:
> 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