DDMMYYYY (UK) Dates mixed with MMDDYY (US) - need all to be MMDDYY

N

niallmurph

New Member
Joined
Jun 11, 2008
Messages
16
I'm dealing with a peculiar csv file which has a date column in which some are noted as Short Dates and others are noted as General.

<table style="border-collapse: collapse; width: 74pt;" border="0" cellpadding="0" cellspacing="0" width="98"><col style="width: 74pt;" width="98"> <tbody><tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt; width: 74pt;" height="19" width="98">Date Submitted</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" align="right" height="19">06/11/2007</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">04-20-07</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">04-19-07</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">04-19-07</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">04-19-07</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt;" align="right" height="19">06/07/2007</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">06-20-07</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">08-20-07</td> </tr> </tbody></table>
The ones noted in the DDMMYYYY format have been picked up incorrectly - the actually should be MMDDYY format.

The MMDDYY dates are in General format, so their conversion to Date format is simple.

So, for example,06/11/2007 is Jun 11th , though it has been (seemingly automatically) registered as a DDMMYYYY date.

I need to it to realise it's a MMDD(YY)YY date. When i convert it to General Text it's becomes 39392, so I cannot seem to be able to make 06/11/2007 (Date) change to 06/11/2007 (Text) either.


Basically, I need the DDMMYYYY dates to become like the MMDDYY dates. Is there a way to make them plain text and not revert to 'numeric dates' - the 'numeric dates' are wrongly becoming DDMMYYYY when they are in actual fact MMDDYYYY.


This is difficult to explain (apologies for my messy attemt), so if it's unclear please let me know and I'll try again.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Hi Richard,

Thank you very much for you almost immediate response!

However, when I do this, the UK dates become US dates.

E.g. 06/11/2007 becomes 11/06/2007.

The problem is that the initial date 06/11/2007 is actually already in the MMDDYYYY format, but Excel sees it as DDMMYYYY.

This is why I need to convert 06/11/2007 (Date) to 06/11/2007 (Text), so I can then make it MDY and the result is 06/11/2007.

Any clearer?
 
Upvote 0
In your original post you said that 06/11/2007 was 11 June 2007, which correctly becomes 11/06/2007 in UK date format when converted. Or am I missing something?
 
Upvote 0
I think you are.

06/11/2007 is a UK date in the sheet.

i.e. Excel thinks it's Nov 6th.

However, this is an error - the date should be Jun 11th.

It should be seen as a US date.
 
Upvote 0
When the day and month are ambiguous (ie the day is less than 13) Excel converts to a date using US formatting. Otherwise the entry is text. So what should be 11 June becomes 6 Nov. Text To Columns converts all the dates from MDY to DMY, so 6 Nov becomes 11 June, which is correct.
 
Upvote 0
niall

I think I might be missing something too.

If I start with this:

<TABLE style="WIDTH: 286pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=381 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=102 x:str="Date Submitted ">Date Submitted </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>Number?</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=115>Full Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=100>Date as number</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39392">06/11/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A2)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A2,"dd mmmm yyyy")'>06 November 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A2,"00000")'>39392</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:str="04-20-07 ">04-20-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A3)" x:bool="FALSE">FALSE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="04-20-07 " x:fmla='=TEXT(A3,"dd mmmm yyyy")'>04-20-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=middle x:fmla='=--TEXT(A3,"00000")' x:err="#VALUE!">#VALUE!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:str="04-19-07 ">04-19-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A4)" x:bool="FALSE">FALSE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="04-19-07 " x:fmla='=TEXT(A4,"dd mmmm yyyy")'>04-19-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=middle x:fmla='=--TEXT(A4,"00000")' x:err="#VALUE!">#VALUE!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:str="04-19-07 ">04-19-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A5)" x:bool="FALSE">FALSE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="04-19-07 " x:fmla='=TEXT(A5,"dd mmmm yyyy")'>04-19-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=middle x:fmla='=--TEXT(A5,"00000")' x:err="#VALUE!">#VALUE!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:str="04-19-07 ">04-19-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A6)" x:bool="FALSE">FALSE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="04-19-07 " x:fmla='=TEXT(A6,"dd mmmm yyyy")'>04-19-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=middle x:fmla='=--TEXT(A6,"00000")' x:err="#VALUE!">#VALUE!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39269">06/07/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A7)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A7,"dd mmmm yyyy")'>06 July 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A7,"00000")'>39269</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:str="06-20-07 ">06-20-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A8)" x:bool="FALSE">FALSE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="06-20-07 " x:fmla='=TEXT(A8,"dd mmmm yyyy")'>06-20-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=middle x:fmla='=--TEXT(A8,"00000")' x:err="#VALUE!">#VALUE!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:str="08-20-07 ">08-20-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A9)" x:bool="FALSE">FALSE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="08-20-07 " x:fmla='=TEXT(A9,"dd mmmm yyyy")'>08-20-07 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=middle x:fmla='=--TEXT(A9,"00000")' x:err="#VALUE!">#VALUE!</TD></TR></TBODY></TABLE>

Then do Data>Text to columns..., as Richard suggested, I get this.

<TABLE style="WIDTH: 286pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=381 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=102 x:str="Date Submitted ">Date Submitted </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>Number?</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=115>Full Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=100>Date as number</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39244">11/06/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A2)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A2,"dd mmmm yyyy")'>11 June 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A2,"00000")'>39244</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39192">20/04/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A3)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A3,"dd mmmm yyyy")'>20 April 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A3,"00000")'>39192</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39191">19/04/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A4)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A4,"dd mmmm yyyy")'>19 April 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A4,"00000")'>39191</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39191">19/04/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A5)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A5,"dd mmmm yyyy")'>19 April 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A5,"00000")'>39191</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39191">19/04/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A6)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A6,"dd mmmm yyyy")'>19 April 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A6,"00000")'>39191</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39240">07/06/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A7)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A7,"dd mmmm yyyy")'>07 June 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A7,"00000")'>39240</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39253">20/06/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A8)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A8,"dd mmmm yyyy")'>20 June 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A8,"00000")'>39253</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 align=right x:num="39314">20/08/2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=middle x:fmla="=ISNUMBER(A9)" x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:fmla='=TEXT(A9,"dd mmmm yyyy")'>20 August 2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=--TEXT(A9,"00000")'>39314</TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
153
dilshod_k
D
P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
204
POLACOLOCO
P
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
383
archibell
A
R
  • Solved
Import Data Date Format Wrong
Replies
9
Views
304
Georgiboy
Georgiboy
C
  • Question
Incorrect date
2 3
Replies
25
Views
498
awoohaw
awoohaw

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

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