Convert a date/time format to a date format

[LITTLETON]

Excel 2007 - 10/14/2011 23:59 PM

I'm having a tough time converting a date/time (MMDDYYYY HHMM PM) to a regular date MMDDYYYY only.

[judgeh59]

would this work? put this in B1

Please Login or Register  to view this content.

EDIT: This will only work if the date/time in A1 is a real excel date/time...

[benishiryo]

welcome to the forum, Littleton. to know if it's a an Excel date recognised by Excel, go to the cell & press CTRL + SHIFT + ~
you should see a number appear. if it remains the same, it's a text. press undo to revert

if it's a real date, it depends on whether you want to present it visually or really remove the time. for the former, right-click the cell & format cells. custom:
MMDDYYYY
for the latter:
=INT(A1)
that will remove the time

if it's not a real date, there are complications. is your date always in MM/DD/YY? like is 1st Feb 2013 shown as 01/02/2013 or 1/2/2013. if it's the former, it's:
=DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))
then format cells