Issue with Filtering Datewise.

I am not understanding what you mean?

Can you give examples of what you expect?
 
=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.
 
View attachment 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.
 
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 :)
 
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.
 
You must log in or register to reply here.
Back
Top