Extract date from SQL timestamp format

[shayma]

Hello,

I have extracted data from SQL DB where one of the columns contains the timestamp of entry in the following format:
08/06/2015 08:10:22 AM

Now I wish to view entries falling on a specific month, i.e. only entries in May for example. However, I can't do this unless I change the format of the date to be DATE rather than timestamp text. How can I do this?

I tried changing the format using custom but nothing would change. Also using text function didn't help.

[FDibbins]

I tried changing the format using custom but nothing would change. Also using text function didn't help.

Thats probably because they are already text

without seeing a sample of what you have, 2 options come to mind...
1. use a helper column with this formula, copied down...
=DATEVALUE(A1)

2. Use Text2Columns...
highlight the data
Data tab/Data Tools/Text2Columns
select Delimited
next/next/ckeck date and select a format/Finished

[shayma]

Let me show you an example of the data that I have

	A	B	C
1	Serial	Name	Date
2	ID1	John	08/06/2015 09:50:05 AM
3	ID2	Mary	25/05/2015 07:26:32 AM
4	ID3	Michael	16/04/2015 12:38:06 PM

Date column is already in Date format (right-click, format, date...etc) but it is not being treated as date (doing month(C2) returns #VALUE! for example, rather than June).

Doing datevalue also returns #VALUE! error, so I'm not sure what I'm missing here.

However, using text2column I was able to separate the cell contents and work from there.


Thanks a lot!

[Richard Buttrey]

Hi,

As Ford has said it's likely that column C values are strings of text rather than date/time values. Don't confuse how a cell is formatted with what it actually contains. You can easily check whether a cell contains a number or text with one of the =ISNUMBER(A1) or =ISTEXT(A1) formulae. The fact that you were able to use Text to columns rather proves this.

The moral is when you extract data from other systems try to ensure that they contain date/time numbers that Excel can use. A lot of systems will produce numbers rather than text, and without nimber values you can't perform date/time arithmetic.