Forum Discussion
Solved
Convert Day/Date/Time cell to Date Only?
How can I convert the following cell of day, date, time to a simple date format?
Current Cell: Sat, Feb 06, 2021, 06:16PM
Goal Format: 2/6/2021
I tried the following formula, but it didn't work: =MONTH(A2)&"/"&DAY(A2)&"/"&YEAR(A2)
The values in column A aret text. not date/time, so MONTH etc. won't work.
Enter the following formula in B2:
=DATEVALUE(MID(A2,10,2)&"-"&MID(A2,6,3)&"-"&MID(A2,14,4))
Format B2 as a date, then fill down.
3 Replies
- without the actual sheet I can't be sure but suspect that column A is TEXT not an Excel recognized Date. But since the format looks perfectly defined you should be able to use:
=MID(A:A,6,12) to give you just the TEXT that is Month, Day, Year
better yet you can add DATEVALUE and then use the number formatting to have excel convert it into an Excel recognized date and have excel number formatting format it how ever you wish:
=DATEVALUE( MID(A:A,6,12) )
And if you really need the output to be text you could
=TEXT(DATEVALUE( MID(A:A,6,12) ),"MM/DD/YYYY") The values in column A aret text. not date/time, so MONTH etc. won't work.
Enter the following formula in B2:
=DATEVALUE(MID(A2,10,2)&"-"&MID(A2,6,3)&"-"&MID(A2,14,4))
Format B2 as a date, then fill down.
Worked beatutifully. Thank you. Cheers.
