Issue with Filtering Datewise.

**shashikanth** · 2013-05-29, 05:13 PM

convert dates to month.xlsx

Data is displayed with date figures on month and month figures by date. Attached is the data.

Please let me know how we can make excel treat the dates as dates and month info as month.

Shashikanth
+91 9620475594
India

**NBVC** · 2013-05-29, 07:17 PM

I am not understanding what you mean?

Can you give examples of what you expect?

**royUK** · 2013-05-29, 08:08 PM

The entries are not actually entered as dates. I suspect they are exported from another program.

**royUK** · 2013-05-29, 08:12 PM

Try

=DATE(YEAR(A2),MONTH(A2),DAY(A2)) in B2 & copy down. Format as mmmm

**shashikanth** · 2013-05-30, 11:03 AM

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

This didn't solve my problem. The data was extracted from our Antivirus Console. Information of all machines which communicated to Antivirus Server. But the issue is: The date(s) turned to Months and Month(s) turned to Dates. We don't want the time. We can delete/remove the time. Only the date field should be corrected. Example from the sheet I attached: The cell A2 has the value - 1/3/2013 11:11 it is not January 3rd. it is March 1st. Like that all dates in A column are in reverse. If you notice, when you add a filter at A1, after December all other entries are not falling under filter correctly. Because it is 13th February 2013. Please help me with some formula to convert the existing Month as date and Date as month. Hope this description help you to understand my problem here.

**shashikanth** · 2013-05-30, 11:11 AM

convert dates to month.xlsx

Thank you all for responding. The data was extracted from our Antivirus Console. Information of all machines which communicated to Antivirus Server. But the issue is: The date(s) turned to Months and Month(s) turned to Dates. We don't want the time. We can delete/remove/ignore the time. Only the date field should be corrected. Example from the sheet I attached: The cell A2 has the value - 1/3/2013 11:11 it is not January 3rd. it is March 1st. Like that all dates in A column are in reverse. If you notice, when you add a filter at A1, after December all other entries are not falling under filter correctly. Because it is 13th February 2013. Please help me with some formula to convert the existing Month as date and Date as month. Hope this description help you to understand my problem here.

**NBVC** · 2013-05-30, 02:18 PM

Ok try this.

Select Column A, then go to Home|Find&Replace|Replace (or CTRL+H)

In the Find What field enter a space followed by and asterisk: " *" (without quotes)

Leave the Replace with field blank... then click Replace All.

Now, format column A as Date, picking the first option *3/14/2001

Now, go to Data|Text to Columns. Click Next, then click Next again. Select Date from the column data format area, then in adjacent drop down, select DMY. Click Finish.

Hopefully that did the job

**royUK** · 2013-05-30, 02:25 PM

That formula worked on your dummy spreadsheet

**NBVC** · 2013-05-30, 03:51 PM

Roy, that formula doesn't work for me either, perhaps because we are on different regional settings... The OP sees the first entry as January 3rd, 2013, but in fact, he wants it to be March 1st, 2013.