Bad problem with the date

[Xenoun]

Hello,
I've constantly encountered problems with the format of date entries in what I am currently working on. However, I was eventually able to fix my 'first run' with a combination of the date format and text to columns and just playing around with it in general. However, the problem has now come back as I need to link my workbook to the workbook where the data comes from and due to that I can't use text to columns.

The basic problem seems to be that excel is reading some of the dates as text, whilst recognising others as proper dates. On the original workbook the dates are displayed in a column, so text to columns wont work there either.
Is there any other fix that anyone knows of which will get excel to recognise the dates as dates rather than text?

 

[Datsmart]

What date format is on each PC?

Generally when you have problems with "some of the dates as text," the Date format of the PC's "Regional Setting" is the problem. Check the Date setting in your computers "Regional Setting".

If you have "English United Kingdom", Excel won't recognize a Date like, "mm/dd/yy".
Conversely, if you use "English United States", Excel doesn't like a Date like, "dd/mm/yy" without it being Text.
To Check your Regional Settings:
Open Control Panel
Click "Regional and Language Options…"
In the "Format" tab, under"Current format:" will be your Regional Setting.
There is a "Customize this format.. Button where you can customize the displayed settings.
Note: you can not use a Euro style date in the English US format.
You have to use either Euro or US.

 

[Xenoun]

I've tried regional settings before...but the problem isnt with the settings or even the date format specifically. Its that excel won't register the date as a date, it just sees it as text. If I change anything it doesn't affect the 'text dates', only the ones that excel registers as a date.

Just in case, I changed my regional settings back to Australian whilst writing this and the dates were unaffected

 

[MDuff]

I've tried regional settings before...but the problem isnt with the settings or even the date format specifically. Its that excel won't register the date as a date, it just sees it as text. If I change anything it doesn't affect the 'text dates', only the ones that excel registers as a date.

Just in case, I changed my regional settings back to Australian whilst writing this and the dates were unaffected[/QUOTE


Well you could run a Date Value formula on the affected cells here is a bit more info. I have also ran macros to remove spaces to fix Text date issues...hope it helps

http://office.microsoft.com/en-us/excel-help/datevalue-HP005209044.aspx

 

[Xenoun]

DATEVALUE gives me the #Value! error. I've also tried that one previously and always only gotten that error. I don't know if i'm using it incorrectly, but it only seems to work if I type the actual date in there rather than a cell reference.

 

[MDuff]

Try this Slect the range an run... this is from an other post to give credit where credit is due

http://www.eggheadcafe.com/software...-remove-space-at-front-and-end-of-a-cell.aspx


Sub dural_hematoma()
For Each R In Selection
If Not R.HasFormula Then
a = R.Address
R.Value = Evaluate("Trim(" & a & ")")
End If
Next
End Sub

 

[Xenoun]

Try this Slect the range an run... this is from an other post to give credit where credit is due

http://www.eggheadcafe.com/software...-remove-space-at-front-and-end-of-a-cell.aspx


Sub dural_hematoma()
For Each R In Selection
If Not R.HasFormula Then
a = R.Address
R.Value = Evaluate("Trim(" & a & ")")
End If
Next
End Sub

I've tried running it and nothing seems to happen. My mouse just changes to the icon that says it's working but nothing changes. Not sure if im using it right?

Also tried the standard TRIM function, but that doesn't seem to have done anything..just basically copied the cells across as there isn't any spaces.

 

[MDuff]

Did you ever get this fixed?

if not try this:

cchoose Text To Columns Command; the Convert Text to Columns Wizard appears (see graphic 2)
2.Choose Fixed Width in 1/3 step
3.Place the Fixed with at the EXACT end of the date and click finsih

this worked for me on a sheet with the same issues you are seeing

 

[Xenoun]

Thanks for the reply, but I did manage to solve this in a different way.

I couldn't use text to columns as the dates were pasted links from another workbook. I managed to record a macro that copied the dates, pasted their values in the next column, used text to columns to format them correctly and then sorted them as I need.

 

[MDuff]

Thanks for the reply, but I did manage to solve this in a different way.

I couldn't use text to columns as the dates were pasted links from another workbook. I managed to record a macro that copied the dates, pasted their values in the next column, used text to columns to format them correctly and then sorted them as I need.

Ok glad it worked