Issue with Filtering Datewise.

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I am not understanding what you mean?

Can you give examples of what you expect?
 

shashikanth

New member
Joined
May 29, 2013
Messages
4
Reaction score
0
Points
0
Age
39
Location
Bangalore, India
=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

New member
Joined
May 29, 2013
Messages
4
Reaction score
0
Points
0
Age
39
Location
Bangalore, India
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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 :)
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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.
 
Top