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?

 

[snjpverma]

did you try to select Date as the format while doing "Text to column"?

another option would be, if your date column is B then
in a helper column (let's assume in Column C)
try this
=datevalue(B2)

once you get it in number format, you can change the format as per your requirement.

Paste value column C and delete Column B

 

[Bedford]

I've tried the text to column, although I can't be sure I'm setting all the settings correctly, not working there. I've also tried the =datevalue formula and it simply returned a #VALUE ? error.
Still stuck with this one...

did you try to select Date as the format while doing "Text to column"?

another option would be, if your date column is B then
in a helper column (let's assume in Column C)
try this
=datevalue(B2)

once you get it in number format, you can change the format as per your requirement.

Paste value column C and delete Column B

 

[jtakw]

Hi,

Try these:

Book1
	A	B	C	D	E
1	28/01/2018		January 28, 2018		TRUE
2			January 28, 2018		TRUE
Sheet19

Cell Formulas
Range	Formula
C1	=TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"mmmm dd, yyyy")+0
C2	=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
E1	=ISNUMBER(C1)
E2	=ISNUMBER(C2)

C1 formula converts your date to format you want and is converted to a number so you can do further calculations with the result.
D1 formula converts your date to number, and you'll need to Custom Format the cell to mmmm dd, yyyy to show in the format you want.

 

[MARZIOTULLIO]

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


wrong formula

 

[Bedford]

I did try what jtakw suggested and the results were a bit off;

in cell A1; 10/20/2017, resulted in; August 10, 2018
in cell A2; 10/23/2017, resulted in; November 10, 2018
in cell A3; 10/26/2017, resulted in; February 10, 2019

do you have any suggestions?


[TABLE="width: 262"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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


wrong formula

 

[MARZIOTULLIO]

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

A​

[/TD]
[TD]

B​

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

1​

[/TD]
[TD]January 28,2018[/TD]
[TD]

28/01/2018​

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


B1=(MONTH(LEFT(A1,SEARCH(" ",A1)-1)&0)&"/"&MID(A1,SEARCH(" ",A1)+1,2)&"/"&RIGHT(A1,4))+0


Custom format B1 dd/mm/yyyy

 

[markmzz]

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?

Maybe this Array Fórmula (use Ctrl+Shift+Enter to enter the formula) in B2 (with your date in A2):

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

Markmzz

 

[Bedford]

Sorry Marziotullio, I'm not sure what you're asking me to do here, ref "A1" contains the data, I'm not exactly sure what you propose I enter into "B1"?

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

A​

[/TD]
[TD]

B​

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

1​

[/TD]
[TD]January 28,2018[/TD]
[TD]

28/01/2018​

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


B1=(MONTH(LEFT(A1,SEARCH(" ",A1)-1)&0)&"/"&MID(A1,SEARCH(" ",A1)+1,2)&"/"&RIGHT(A1,4))+0


Custom format B1 dd/mm/yyyy

 

[Bedford]

I gave this a shot, on a mac, to enter as array on mac it's holding command key+enter, but the result in B2 was an error, #VALUE!

Maybe this Array Fórmula (use Ctrl+Shift+Enter to enter the formula) in B2 (with your date in A2):

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

Markmzz