Date Formatting

[Bedford]

When downloading .csv files with a date range in a column separated as; 28/01/2018 I can't seem to change the date format to look as; January 28, 2018. I've tried copying and pasting as values, some text to column options but there doesn't seem to be an easy fix?
Any suggestions?

 

[Bedford]

I tried the modified array, excel is giving me an error, "The formula you typed contains an error."
I can confirm the date format of the Mac OS is mm/dd/yyyy

Try this small modification in the array formula:

1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000")))),
MAX(IF(ISNUMBER(1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000"))))-
MIN(IF(ISNUMBER(1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000"))))+1)

Ps: verify if your date configuration in your Mac OS (not in Excel) is mm/dd/yyyy.

[TABLE="class: grid, width: 412"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Date Like Txt or Other[/TD]
[TD]DateLikeNumber[/TD]
[TD]DateLikeDate[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]09/21/2018[/TD]
[TD="align: right"]43364[/TD]
[TD="align: right"]09/21/2018[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]10/01/2018[/TD]
[TD="align: right"]43374[/TD]
[TD="align: right"]10/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]11/01/2018[/TD]
[TD="align: right"]43405[/TD]
[TD="align: right"]11/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]12/31/2018[/TD]
[TD="align: right"]43465[/TD]
[TD="align: right"]12/31/2018[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]09/02/2018[/TD]
[TD="align: right"]43345[/TD]
[TD="align: right"]09/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]12/27/2018[/TD]
[TD="align: right"]43461[/TD]
[TD="align: right"]12/27/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]11/02/2018[/TD]
[TD="align: right"]43406[/TD]
[TD="align: right"]11/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]12/02/2018[/TD]
[TD="align: right"]43436[/TD]
[TD="align: right"]12/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]09/03/2018[/TD]
[TD="align: right"]43346[/TD]
[TD="align: right"]09/03/2018[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]10/03/2018[/TD]
[TD="align: right"]43376[/TD]
[TD="align: right"]10/03/2018[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]11/03/2018[/TD]
[TD="align: right"]43407[/TD]
[TD="align: right"]11/03/2018[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]12/23/2018[/TD]
[TD="align: right"]43457[/TD]
[TD="align: right"]12/23/2018[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]09/04/2018[/TD]
[TD="align: right"]43347[/TD]
[TD="align: right"]09/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]10/04/2018[/TD]
[TD="align: right"]43377[/TD]
[TD="align: right"]10/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]11/14/2018[/TD]
[TD="align: right"]43418[/TD]
[TD="align: right"]11/14/2018[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]12/04/2018[/TD]
[TD="align: right"]43438[/TD]
[TD="align: right"]12/04/2018[/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*********************[/TD]
[TD]****************[/TD]
[TD]*************[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[MARZIOTULLIO]

my post #7 is wrong

try

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]01/28/2018[/TD]
[TD]

January 28,2018​

[/TD]
[/TR]
</tbody>[/TABLE]


b1=TEXT(SUBSTITUTE(A1,"/","")+0,"00-00-0000")+0 custom format for b1 mmmm dd,yyyy

 

[Bedford]

Strangely I get a #VALUE ! error when using this formula?

my post #7 is wrong

try

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]01/28/2018[/TD]
[TD]

January 28,2018​

[/TD]
[/TR]
</tbody>[/TABLE]


b1=TEXT(SUBSTITUTE(A1,"/","")+0,"00-00-0000")+0 custom format for b1 mmmm dd,yyyy

 

[markmzz]

I tried the modified array, excel is giving me an error, "The formula you typed contains an error."
I can confirm the date format of the Mac OS is mm/dd/yyyy

Sorry, my mistake, try this:

=1*MID(A2,
MIN(IF(ISNUMBER(1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000")))),
MAX(IF(ISNUMBER(1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000"))))-
MIN(IF(ISNUMBER(1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000"))))+1)

Markmzz
​

 

[Bedford]

I accounted for the missing "=" and inserted it myself, but there still seems to be a problem with the formula, excel is telling; "The formula you typed contains an error."

Sorry, my mistake (I forgot the = in my formula), try this:

=1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000")))),
MAX(IF(ISNUMBER(1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000"))))-
MIN(IF(ISNUMBER(1*MID(A2,ROW(INDIRECT("1:1000")),1)),ROW(INDIRECT("1:1000"))))+1)

Markmzz
​

 

[markmzz]

I accounted for the missing "=" and inserted it myself, but there still seems to be a problem with the formula, excel is telling; "The formula you typed contains an error."

Look at the formula again (Isn't only the =).

Markmzz

 

[MARZIOTULLIO]

http://www.filedropper.com/book1_6

this is the file

 

[Bedford]

Ok, that was strange, clicked on the link and went to sign on as new user, this is the message that popped up; [h=1]Your connection is not private[/h]Attackers might be trying to steal your information from filedropper.com (for example, passwords, messages, or credit cards). Learn more
NET::ERR_CERT_DATE_INVALID

http://www.filedropper.com/book1_6

this is the file

 

[Bedford]

Sorry Markmzz, you've got me stumped, I'm lost as to what I'm missing with this?

Look at the formula again (Isn't only the =).

Markmzz

 

[Paul Ked]

This will get the date value of the text string:

=DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,2)),VALUE(MID(A1,4,2)))