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.

	A	B	C
1	Date Like Txt or Other	DateLikeNumber	DateLikeDate
2	09/21/2018	43364	09/21/2018
3	10/01/2018	43374	10/01/2018
4	11/01/2018	43405	11/01/2018
5	12/31/2018	43465	12/31/2018
6	09/02/2018	43345	09/02/2018
7	12/27/2018	43461	12/27/2018
8	11/02/2018	43406	11/02/2018
9	12/02/2018	43436	12/02/2018
10	09/03/2018	43346	09/03/2018
11	10/03/2018	43376	10/03/2018
12	11/03/2018	43407	11/03/2018
13	12/23/2018	43457	12/23/2018
14	09/04/2018	43347	09/04/2018
15	10/04/2018	43377	10/04/2018
16	11/14/2018	43418	11/14/2018
17	12/04/2018	43438	12/04/2018
****	*********************	****************	*************

<tbody>
</tbody>

Markmzz

 

[MARZIOTULLIO]

my post #7 is wrong

try

	A​	B​
1​	01/28/2018	January 28,2018​

<tbody>
</tbody>

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

	A​	B​
1​	01/28/2018	January 28,2018​

<tbody>
</tbody>

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