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?

 

[jtakw]

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"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

In your OP, you have 28/01/2018, which is day/month/year, now you have it Reversed, month/day/year, which is it?

 

[markmzz]

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!

Hello!

Here all is ok. Look at this:

[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] 01/09/2018[/TD]
[TD="align: right"]43344[/TD]
[TD="align: right"]01/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]01-10-2018[/TD]
[TD="align: right"]43374[/TD]
[TD="align: right"]01/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]1-11-2018[/TD]
[TD="align: right"]43405[/TD]
[TD="align: right"]01/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1/12/2018[/TD]
[TD="align: right"]43435[/TD]
[TD="align: right"]01/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]2/9/18[/TD]
[TD="align: right"]43345[/TD]
[TD="align: right"]02/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]3 dez 2018[/TD]
[TD="align: right"]43437[/TD]
[TD="align: right"]03/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]2/12/2018[/TD]
[TD="align: right"]43436[/TD]
[TD="align: right"]02/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] 3/9/2018[/TD]
[TD="align: right"]43346[/TD]
[TD="align: right"]03/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]3-10-2018[/TD]
[TD="align: right"]43376[/TD]
[TD="align: right"]03/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]3/11/2018[/TD]
[TD="align: right"]43407[/TD]
[TD="align: right"]03/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD] 3/12/2018[/TD]
[TD="align: right"]43437[/TD]
[TD="align: right"]03/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]4/9/2018[/TD]
[TD="align: right"]43347[/TD]
[TD="align: right"]04/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]4-10-2018[/TD]
[TD="align: right"]43377[/TD]
[TD="align: right"]04/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]4-11-2018[/TD]
[TD="align: right"]43408[/TD]
[TD="align: right"]04/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]04/12/2018[/TD]
[TD="align: right"]43438[/TD]
[TD="align: right"]04/12/2018[/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*********************[/TD]
[TD]****************[/TD]
[TD]*************[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[Bedford]

actually, in the sheet in both the data column and where I entered your formula; =TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"mmmm dd, yyyy")+0

the result is as in my previous post, both the data and result are in month/day/year format

In your OP, you have 28/01/2018, which is day/month/year, now you have it Reversed, month/day/year, which is it?

 

[jtakw]

actually, in the sheet in both the data column and where I entered your formula; =TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"mmmm dd, yyyy")+0

the result is as in my previous post, both the data and result are in month/day/year format

So, do you mean your data IS NOT like in your OP (28/01/2018), but like:

Book1
	A
3	10/20/2017
4	10/23/2017
5	10/26/2017
Sheet19

as in your Post #6 ?

If so, in an unused cell, reference one of the cells with data (i.e. A3), put in =ISNUMBER(A3)
What's the result? TRUE or FALSE?
Because if the data is in the format of mm/dd/yyyy, Excel should recognize it as a DATE, unless it's TEXT.

 

[Bedford]

Goodness, I'm not sure why it's not working for me, data is in "A2", and I've entered the array in "B2", confirming the formula has {} curly brackets....confusing.

Hello!

Here all is ok. Look at this:

[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] 01/09/2018[/TD]
[TD="align: right"]43344[/TD]
[TD="align: right"]01/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]01-10-2018[/TD]
[TD="align: right"]43374[/TD]
[TD="align: right"]01/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]1-11-2018[/TD]
[TD="align: right"]43405[/TD]
[TD="align: right"]01/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1/12/2018[/TD]
[TD="align: right"]43435[/TD]
[TD="align: right"]01/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]2/9/18[/TD]
[TD="align: right"]43345[/TD]
[TD="align: right"]02/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]3 dez 2018[/TD]
[TD="align: right"]43437[/TD]
[TD="align: right"]03/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]2/12/2018[/TD]
[TD="align: right"]43436[/TD]
[TD="align: right"]02/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] 3/9/2018[/TD]
[TD="align: right"]43346[/TD]
[TD="align: right"]03/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]3-10-2018[/TD]
[TD="align: right"]43376[/TD]
[TD="align: right"]03/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]3/11/2018[/TD]
[TD="align: right"]43407[/TD]
[TD="align: right"]03/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD] 3/12/2018[/TD]
[TD="align: right"]43437[/TD]
[TD="align: right"]03/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]4/9/2018[/TD]
[TD="align: right"]43347[/TD]
[TD="align: right"]04/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]4-10-2018[/TD]
[TD="align: right"]43377[/TD]
[TD="align: right"]04/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]4-11-2018[/TD]
[TD="align: right"]43408[/TD]
[TD="align: right"]04/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]04/12/2018[/TD]
[TD="align: right"]43438[/TD]
[TD="align: right"]04/12/2018[/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*********************[/TD]
[TD]****************[/TD]
[TD]*************[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[Bedford]

Yes, exactly as you have it below. I'd send a picture but attaching images in this forum seems to require the image to have a URL?

So, do you mean your data IS NOT like in your OP (28/01/2018), but like:

	A

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]10/20/2017[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10/23/2017[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10/26/2017[/TD]

</tbody>

Sheet19​

as in your Post #6 ?

If so, in an unused cell, reference one of the cells with data (i.e. A3), put in =ISNUMBER(A3)
What's the result? TRUE or FALSE?
Because if the data is in the format of mm/dd/yyyy, Excel should recognize it as a DATE, unless it's TEXT.

 

[jtakw]

What's the answer to ISNUMBER?

 

[Bedford]

The result of =ISNUMBER is "FALSE"

Yes, exactly as you have it below. I'd send a picture but attaching images in this forum seems to require the image to have a URL?

 

[markmzz]

Goodness, I'm not sure why it's not working for me, data is in "A2", and I've entered the array in "B2", confirming the formula has {} curly brackets....confusing.

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

 

[jtakw]

The result of =ISNUMBER is "FALSE"

Then they're TEXT dates, try this:

Book1
	A	B	C
3	10/20/2017		October 20, 2017
4	10/23/2017		October 23, 2017
5	10/26/2017		October 26, 2017
Sheet19

Cell Formulas
Range	Formula
A3	=" 10/20/2017 "
C3	=TEXT(DATE(RIGHT(TRIM(A3),4),LEFT(TRIM(A3),2),MID(TRIM(A3),4,2)),"mmmm dd, yyyy")+0

Formula copied down.