Weird problem

[Xenoun]

Hi, i'm organising and graphing some data and have encountered a strange problem with the dates column

Some of the entries look like this:

<TABLE style="WIDTH: 72pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=96><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20 width=96>08/17/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/17/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/18/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/18/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/18/2010</TD></TR></TBODY></TABLE>

Which is the US format. I want this in AUS date format but anything I do to formatting, or using the TEXT formula or any other one i've tried doesnt have an effect. I have other date entries which were originally US in the same column and they were formatted to AUS automatically with the format cells option.

The cells i'm having a problem with appear to be text, but if I refresh the cell nothing happens. The text formula is able to re-arrange the normal dates into whatever I want, but these erroneous cells stay the same for whatever I do

 

[edokhotnik]

Hi, i'm organising and graphing some data and have encountered a strange problem with the dates column

Some of the entries look like this:

<TABLE style="WIDTH: 72pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=96><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20 width=96>08/17/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/17/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/18/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/18/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/18/2010</TD></TR></TBODY></TABLE>

Which is the US format. I want this in AUS date format but anything I do to formatting, or using the TEXT formula or any other one i've tried doesnt have an effect. I have other date entries which were originally US in the same column and they were formatted to AUS automatically with the format cells option.

The cells i'm having a problem with appear to be text, but if I refresh the cell nothing happens. The text formula is able to re-arrange the normal dates into whatever I want, but these erroneous cells stay the same for whatever I do

Have you tried the CLEAN or TRIM function to eliminate any extra spaces in the cells? Also, try using Paste Special - Multiply to multiply all values by 1.

 

[Xenoun]

I dont think clean or trim will help me as the dates dont have spaces or anything like that. I tried the paste special, multiply like you suggested and nothing happened...didnt even paste.

I've discovered that the dates are text. I've looked at that before and tried everything i've seen suggested and nothing happened so I thought it was something else. But I just entered a date into a cell formatted as general, then changed it to date and nothing happened, same as what happens with the other dates.

So basically I need a method that works to change text date values into real dates


Edit: I'm trying to use DATEVALUE which I found earlier as a suggested method to fix this but it just returns #Value!. Does anyone know how to use this correctly?

 

[Haseeb Avarakkan]

Try,

Select the range , press Ctrl+F --> Find What: / --> Replace with: /

Then click Replace All

 

[Xenoun]

ADVERTISEMENT

Didn't work...

Think I know what is going wrong. The date formats that are being read as text are in the US format. i.e. 2/19/10. When I type that in, excel reads it as text.
If I type 19/2/10 in any cell, even one formatted as general excel will change the format to date and read what I typed as a date.

Yet I know when excel is in US date settings and you type an AUS date it converts it to US. Why isn't it doing the reverse for me here?

 

[Marcelo Branco]

Hi,

Try this
Select the dates
Data>Text to Columns
Next
Next
change DMA to MDA
Finish

The dates will appear in dd/mm/yyyy (i hope It worked for me)

HTH

M.

 

[Xenoun]

ADVERTISEMENT

Ok, I changed my column to US date to test it out...typed in an AUS date and it changed to US. But when I typed a US date it came up as text...


So I went into windows regional setting and changed it to US instead of AUS.....Now everything works fine, its even changed those text dates that were in US format into AUS date format

 

[Xenoun]

Hi,

Try this
Select the dates
Data>Text to Columns
Next
Next
change DMA to MDA
Finish

The dates will appear in dd/mm/yyyy (i hope It worked for me)

HTH

M.

I did that several times before I just solved this...it didnt change the dates that it read as text

 

[Haseeb Avarakkan]

....

 

[Marcelo Branco]

I did that several times before I just solved this...it didnt change the dates that it read as text

Well...it worked perfect for me.

I've just copied your sample above pasted in Excel (it appeared as text, of course) and used Text to Columns using MDA the american setting

It Worked fine (my regional setting for dates is dd/mm/yyyy).

M.