Date format 8/8/08 (m/m/yyyy) to dd/mm/yyy

bombay69

New Member
Joined
Jun 30, 2005
Messages
48
Hi Guys,
I have imported some raw unformatted data into in excel and the date comes in as eg 8/8/08, 6/12/08, 12/9/08 in the US format. When I do a date format on it 6/12/08 could be 6th Jan 2008 instead of 12 Dec 2008 etc.. Is there a way of acurately formatting as dd/mm/yyyy?
Thanks
 

bombay69

New Member
Joined
Jun 30, 2005
Messages
48
Re: Date format 8/8/08 (m/d/yyyy) to dd/mm/yyyy

sorry Date format 8/8/08 (m/d/yyyy) to dd/mm/yyyy of course.
 

bombay69

New Member
Joined
Jun 30, 2005
Messages
48
Re: Date format 8/8/08 (m/d/yyyy) to dd/mm/yyyy

The dates come in as 7/12/2007, 17/12/2008, 15/2/2006 etc...ie missing a zero before single digits - I need to convert them to either dd/mm/yyyy or mm/dd/yyyy
Thanks - apologies for the dodgy English
 

theozz

Active Member
Joined
Jun 11, 2007
Messages
328
Re: Date format 8/8/08 (m/d/yyyy) to dd/mm/yyyy

Hi ! Here Suggest to you

<table style="border-collapse: collapse;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> Nice Question Thanks.
DataSheet= Sheet1</td></tr><tr><td width="30" align="right"><table rowspan="11" width="30" cellspacing="1"><tbody bgcolor="#ffffff"><tr height="18"><td></td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">1 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">2 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">3 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">4 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">5 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">6 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">7 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">8 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">9 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">10 </td></tr></tbody></table></td><td width="424"><table colspan="4" rowspan="5" width="424" bgcolor="#939393" border="0" cellspacing="1"><col width="57"><col width="125"><col width="115"><col width="127"><tbody bgcolor="#ffffff"><tr height="18"><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">A</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">B</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">C</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">D</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">
</td><td rowspan="2" align="center" bgcolor="#ffffff">before</td><td colspan="2" align="center" bgcolor="#ffffff">after</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">String</td><td align="left" bgcolor="#ffffff">Date</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">
</td><td colspan="2" align="left" bgcolor="#ffffff">B4 His Format= m/d/yy</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">Date</td><td align="right" bgcolor="#ffffff">8/8/08</td><td align="left" bgcolor="#ffffff">
</td><td align="right" bgcolor="#ffffff">08/08/2008</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">Date</td><td align="right" bgcolor="#ffffff">9/25/08</td><td align="left" bgcolor="#ffffff">
</td><td align="right" bgcolor="#ffffff">25/09/2008</td></tr></tbody></table><table colspan="4" rowspan="5" width="424" bgcolor="#939393" border="0" cellspacing="1"><col width="57"><col width="125"><col width="115"><col width="127"><tbody bgcolor="#ffffff"><tr height="18"><td align="left" bgcolor="#ffffff">String</td><td align="left" bgcolor="#ffffff">8/8/08</td><td align="left" bgcolor="#ffffff">08/8/8</td><td align="right" bgcolor="#ffffff">08/08/2008</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">String</td><td align="left" bgcolor="#ffffff">9/25/08</td><td align="left" bgcolor="#ffffff">08/9/25</td><td align="right" bgcolor="#ffffff">25/09/2008</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">String</td><td align="left" bgcolor="#ffffff">7/12/2007</td><td align="left" bgcolor="#ffffff">2007/7/12</td><td align="right" bgcolor="#ffffff">12/07/2007</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">String</td><td align="left" bgcolor="#ffffff">17/12/2008</td><td align="left" bgcolor="#ffffff">2008/7/12</td><td align="right" bgcolor="#ffffff">12/07/2008</td></tr><tr height="18"><td colspan="2" align="right" bgcolor="#ffffff">B9 His Format= General</td><td colspan="2" align="right" bgcolor="#ffffff">D4 His Format= dd/mm/yyyy</td></tr></tbody></table></td></tr></tbody></table>
<colspan=14 width="14" rowspan="11" height="198"></colspan=14><table style="border-collapse: collapse;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="14" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)</td></tr><tr height="24"><td width="4%" align="center" bgcolor="#d3d3d3">No</td><td width="9%" align="center" bgcolor="#d3d3d3">Addr'</td><td width="65%" align="center" bgcolor="#d3d3d3"> If use below Formula, You'll Get Result as Right</td><td width="15%" align="center" bgcolor="#d3d3d3">Result</td><td align="center" bgcolor="#d3d3d3">Formula's</td></tr><tr height="20"><td align="center">1</td><td align="center">D4</td><td>=DATE(YEAR(B4),MONTH(B4),DAY(B4))</td><td align="right">08/08/2008</td><td align="right"><form name="PrNames1"><input onclick='window.clipboardData.setData("Text","=DATE(YEAR(B4),MONTH(B4),DAY(B4))");' value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center"></td><td>D4 His Formula Used This Cell -> D4:D5</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">3</td><td align="center">C6</td><td>=RIGHT(B6,2)&"/"&LEFT(B6,LEN(B6)-3)</td><td align="left">08/8/8</td><td align="right"><form name="PrNames3"><input onclick='window.clipboardData.setData("Text","=RIGHT(B6,2)&\"/\"&LEFT(B6,LEN(B6)-3)");' value="Do Copy" name="MyNames3" type="button"></form></td></tr><tr height="20"><td align="center">4</td><td align="center"></td><td>C6 His Formula Used This Cell -> C6:C7</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">5</td><td align="center">D6</td><td>=(RIGHT(B6,2)&"/"&LEFT(B6,LEN(B6)-3))*1</td><td align="right">08/08/2008</td><td align="right"><form name="PrNames5"><input onclick='window.clipboardData.setData("Text","=(RIGHT(B6,2)&\"/\"&LEFT(B6,LEN(B6)-3))*1");' value="Do Copy" name="MyNames5" type="button"></form></td></tr><tr height="20"><td align="center">6</td><td align="center"></td><td>D6 His Formula Used This Cell -> D6:D7</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">7</td><td align="center">C8</td><td>=RIGHT(B8,4)&"/"&SUBSTITUTE(IF(LEFT(B8,FIND("/",B8)-1)*1>12,MID(B8,2,256),B8),RIGHT(B8,5),"")</td><td align="left">2007/7/12</td><td align="right"><form name="PrNames7"><input onclick='window.clipboardData.setData("Text","=RIGHT(B8,4)&\"/\"&SUBSTITUTE(IF(LEFT(B8,FIND(\"/\",B8)-1)*1>12,MID(B8,2,256),B8),RIGHT(B8,5),\"\")");' value="Do Copy" name="MyNames7" type="button"></form></td></tr><tr height="20"><td align="center">8</td><td align="center"></td><td>C8 His Formula Used This Cell -> C8:C9</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">9</td><td align="center">D8</td><td>=(RIGHT(B8,4)&"/"&SUBSTITUTE(IF(LEFT(B8,FIND("/",B8)-1)*1>12,MID(B8,2,256),B8),RIGHT(B8,5),""))*1</td><td align="right">12/07/2007</td><td align="right"><form name="PrNames9"><input onclick='window.clipboardData.setData("Text","=(RIGHT(B8,4)&\"/\"&SUBSTITUTE(IF(LEFT(B8,FIND(\"/\",B8)-1)*1>12,MID(B8,2,256),B8),RIGHT(B8,5),\"\"))*1");' value="Do Copy" name="MyNames9" type="button"></form></td></tr><tr height="20"><td align="center">10</td><td align="center"></td><td>D8 His Formula Used This Cell -> D8:D9</td><td align="left"></td><td></td></tr><tr height="24"><td colspan="14">
If this is not wanted answer, give more detail of Questions

How about this suggest?

</td></tr></tbody></table>
 

bombay69

New Member
Joined
Jun 30, 2005
Messages
48
Hi,
Found the issue. The dates were delimited even though they appeared as 8/8/2006 etc... in cells. Click Data>Text To Columns --this delimited the format allowing me to format the cells as dates as standard. Was lucky - stumbled across it.
 

Forum statistics

Threads
1,085,333
Messages
5,383,021
Members
401,812
Latest member
topherj09

Some videos you may like

This Week's Hot Topics

Top