SOLVED

Convert Day/Date/Time cell to Date Only?

%3CLINGO-SUB%20id%3D%22lingo-sub-2646367%22%20slang%3D%22en-US%22%3EConvert%20Day%2FDate%2FTime%20cell%20to%20Date%20Only%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2646367%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20convert%20the%20following%20cell%20of%20day%2C%20date%2C%20time%20to%20a%20simple%20date%20format%3F%3CBR%20%2F%3E%3CBR%20%2F%3ECurrent%20Cell%3A%20Sat%2C%20Feb%2006%2C%202021%2C%2006%3A16PM%3CBR%20%2F%3EGoal%20Format%3A%202%2F6%2F2021%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20the%20following%20formula%2C%20but%20it%20didn't%20work%3A%26nbsp%3B%3CSPAN%3E%3DMONTH(A2)%26amp%3B%22%2F%22%26amp%3BDAY(A2)%26amp%3B%22%2F%22%26amp%3BYEAR(A2)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen_Shot_2021-08-13_at_12_21_03_AM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303133i56EAFC8599F37496%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen_Shot_2021-08-13_at_12_21_03_AM.png%22%20alt%3D%22Screen_Shot_2021-08-13_at_12_21_03_AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2646367%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2646461%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Day%2FDate%2FTime%20cell%20to%20Date%20Only%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2646461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127465%22%20target%3D%22_blank%22%3E%40relaunch96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20values%20in%20column%20A%20aret%20text.%20not%20date%2Ftime%2C%20so%20MONTH%20etc.%20won't%20work.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20B2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DDATEVALUE(MID(A2%2C10%2C2)%26amp%3B%22-%22%26amp%3BMID(A2%2C6%2C3)%26amp%3B%22-%22%26amp%3BMID(A2%2C14%2C4))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20B2%20as%20a%20date%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2646472%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Day%2FDate%2FTime%20cell%20to%20Date%20Only%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2646472%22%20slang%3D%22en-US%22%3E%3CP%3EWorked%20beatutifully.%20Thank%20you.%20Cheers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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)

 

Screen_Shot_2021-08-13_at_12_21_03_AM.png

 

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@relaunch96 

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.

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")