export in text and functionality

[xgary.wilsonx]

Good afternoon

I have an extract of data that i wish to filter/search etc, however the driver is the date which is in a text format. I have tried unsuccessfully to alter the format of the date but i always seem draw a blank. The data looks something like this '01/12/2010 13:45:08'

Can anybody suggest a method of altering this data so that i can search by date, such as 01/12/2010 or 40513

many thanks in advance

GW

 

[wardy0601]

What format do you want the date to appear in on your spreadsheet?

Wardy..

 

[xgary.wilsonx]

What format do you want the date to appear in on your spreadsheet?

Wardy..

Ideally date format, or a format that can be switched into date

Gary

 

[taurean]

Try the following and see if it works for you.
1. Select the column where these dates are.
2. Do text to column and specify the format as "Date"
This should convert the data to dates.

 

[xgary.wilsonx]

Thanks but already tried the obvious, The text string '01/12/2010 13:45:08' cannot be used for analysis by simply changing the format, i need to pick out the date as a date or number - 40513 so that a lookup or index formula will be able to identify and use it

Thanks for all your help so far

 

[wardy0601]

If you just want to pick the date out for use in a lookup function, it can be done as follows:

Excel Workbook
	A	B
13	01 Dec 2010 13:45:08	01-Dec-10
14
Sheet1

It is picked out using the left function, which creates the date as a text string, you then multiply it by 1 or add 0 to it to convert it back to a number. Obviously the number can then be formatted as a date and used in any formula.

Wardy.

 

[xgary.wilsonx]

Many thanks 'Wardy', you rock!

Gary

 

[taurean]

You can also try:
=INT(A1)
where A1 contains your Text Data. You can format the cell as you want to.

 

[xgary.wilsonx]

Tried and tested, works also, many thanks