Date Format query

[subhransuroy]

Hi

I have below data which generates in skewed date format some come with 4 digit year and some with 02 Digit year .

I am also unable to convert the Date Text into date . Tried all your earlier tips .

I am using Excel 2003

would you be able to help in converting all in an uniform format .

<table width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" align="right" height="17">2009</td> <td class="xl24" style="width: 48pt;" width="64" align="right">3/7/2008</td> <td style="width: 48pt;" width="64">Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">3/7/2008</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">2/1/2009</td> <td>Hi - Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>26/11/08</td> <td>Hi Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">5/8/2008</td> <td>Dinner</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">5/8/2008</td> <td>Reception</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>26/11/08</td> <td>Hi Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">5/12/2008</td> <td>Dinner</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">5/12/2008</td> <td>Hi Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>26/11/08</td> <td>Hi Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Hi Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Hi Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Hi-Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Hi-Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>18/12/08</td> <td>Dinner</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Hi-Tea</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>18/12/08</td> <td>Reception</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>Exhibition - Day 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>PMT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>Exhibition - Day 4 (Last Day)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>PMT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>Exhibition - Day 3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>Lunch</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>Setup - Day 1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>Lunch</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>Lunch</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>27/06/08</td> <td>PMT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">8/1/2009</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">12/3/2008</td> <td>Reception</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td class="xl24" align="right">12/3/2008</td> <td>Dinner buffet</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/08/08</td> <td>Dinner</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/08/08</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/12/08</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/08/08</td> <td>Reception</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>20/08/08</td> <td>Break</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>18/11/08</td> <td>Lunch</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>18/11/08</td> <td>Meeting</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2009</td> <td>18/11/08</td> <td>Press Conference</td> </tr> </tbody></table>

 

[Peter_SSs]

Welcome to the MrExcel board!

I cannot tell if that data is all in one column or several. If several, what columns and where does the data break?

If you want to be able to post better screen shots, investigate these .

Excel jeanie
or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post
or
Borders-Copy-Paste

Test them in the Test Here forum.

 

[subhransuroy]

Thanks Peter for your reply .

The challenge is with the left aligned dates and the ones with 02 digit year. if I run =Datevalue the same returns an error . Evaluate formula displays DATEVALUE("26/11/08").

In an other evaluation the message is " there is a constant in the formula"

regards
Subhransu

 

[Peter_SSs]

You can't link to an image on your own computer. In any case a link to an image is not as useful as using one of the methods I suggested since the data cannot be easily copied from the image to a test worksheet. The easier you make it for helpers, the more help you generally get.