Regarding Date Format

[Allwinv]

Hi Excel Experts,

In one of the Cell, i have the data as 13/6/2018 2:38 PM. But i want to make this data as 13/06/2018. I entered formula as =DATE(YEAR(A1),MONTH(A1),DAY(A1)). But i am getting the error due to AM and PM available in cell. I removed AM and PM in the cell (using the replace Option) and applied the formula as =DATE(YEAR(A1),MONTH(A1),DAY(A1)). But still not working for the cells which are AM and PM data.

Can you please help.

Thanks.

 

[Rick Rothstein]

Does this macro fix your problem (change the red highlighted "A" to the column letter designation for the column where your dates are located)...

[table="width: 500"]
[tr]
	[td]Sub FixDates()
  Columns("[B][COLOR="#FF0000"]A[/COLOR][/B]").TextToColumns , xlDelimited, , , False, False, False, True, False, FieldInfo:=Array(Array(1, 4), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True
End Sub[/td]
[/tr]
[/table]

NOTE: This macro overwrites your original data with the dates represented by your "text dates".

 

[Allwinv]

Hi,

Thank you for your reply.

Can i share my excel data file to your mail. You can check and let me know what is the problem.

I am not ware of macros in excel.

Pls help.