Date Formatting

Bedford

Board Regular
Joined
Feb 3, 2015
Messages
94
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?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
Office Version
  1. 2016
Platform
  1. Windows
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
 
Last edited:

Bedford

Board Regular
Joined
Feb 3, 2015
Messages
94
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Try these:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">28/01/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">January 28, 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">January 28, 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet19</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=TEXT(<font color="Blue">DATE(<font color="Red">RIGHT(<font color="Green">A1,4</font>),MID(<font color="Green">A1,4,2</font>),LEFT(<font color="Green">A1,2</font>)</font>),"mmmm dd, yyyy"</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=DATE(<font color="Blue">RIGHT(<font color="Red">A1,4</font>),MID(<font color="Red">A1,4,2</font>),LEFT(<font color="Red">A1,2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=ISNUMBER(<font color="Blue">C1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=ISNUMBER(<font color="Blue">C2</font>)</td></tr></tbody></table></td></tr></table><br />

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

Well-known Member
Joined
Aug 22, 2015
Messages
767

ADVERTISEMENT


<tbody>
</tbody>


wrong formula

 
Last edited:

Bedford

Board Regular
Joined
Feb 3, 2015
Messages
94
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?



<colgroup><col><col><col></colgroup><tbody>
</tbody>



<tbody>
</tbody>


wrong formula

 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767

ADVERTISEMENT

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

<tbody>
</tbody>


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

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
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

Board Regular
Joined
Feb 3, 2015
Messages
94
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"?



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

<tbody>
</tbody>


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

Board Regular
Joined
Feb 3, 2015
Messages
94
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,503
Messages
5,529,248
Members
409,858
Latest member
mr20201
Top