export in text and functionality

xgary.wilsonx

New Member
Joined
Dec 17, 2009
Messages
40
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

Board Regular
Joined
Sep 24, 2011
Messages
117
What format do you want the date to appear in on your spreadsheet?

Wardy..
 

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
365, 2016
Platform
Windows
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

New Member
Joined
Dec 17, 2009
Messages
40
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

Board Regular
Joined
Sep 24, 2011
Messages
117
If you just want to pick the date out for use in a lookup function, it can be done as follows:

Excel Workbook
AB
1301 Dec 2010 13:45:08
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.
 

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
365, 2016
Platform
Windows
You can also try:
=INT(A1)
where A1 contains your Text Data. You can format the cell as you want to.
 

Forum statistics

Threads
1,082,576
Messages
5,366,419
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top