Date Formatting

B

Bedford

Active Member
Joined
Feb 3, 2015
Messages
333
Office Version
  1. 365
Platform
  1. MacOS
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?
 
Sort by date Sort by votes
Bedford said:
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]
Click to expand...

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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Bedford said:
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!
Click to expand...

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
 
Upvote 0
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


jtakw said:
In your OP, you have 28/01/2018, which is day/month/year, now you have it Reversed, month/day/year, which is it?
Click to expand...
 
Upvote 0
Bedford said:
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
Click to expand...

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


Book1
A
310/20/2017
410/23/2017
510/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.
 
Upvote 0
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.

markmzz said:
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
Click to expand...
 
Upvote 0
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?

jtakw said:
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.
Click to expand...
 
Upvote 0
Bedford said:
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.
Click to expand...

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
 
Upvote 0
Bedford said:
The result of =ISNUMBER is "FALSE"
Click to expand...

Then they're TEXT dates, try this:


Book1
ABC
310/20/2017October 20, 2017
410/23/2017October 23, 2017
510/26/2017October 26, 2017
Sheet19
Cell Formulas
RangeFormula
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA User form - UK Date Entry converts to USA Date Format
Replies
1
Views
314
Alex Blakenburg
A
dezibluenose
  • Solved
Countifs formula not picking up dates, when reference columns show as dates?!
Replies
7
Views
459
Alex Blakenburg
A
R
  • Solved
Cells in the same column showing different date format
Replies
5
Views
81
Peter_SSs
Peter_SSs
D
  • Solved
Timestamp reads as text when converting to Date
Replies
6
Views
85
Joe4
Joe4
S
  • Solved
Date missing leading zero even with date format of dd/mm/yyyy in Excel
Replies
2
Views
558
Surreybloke
S

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top