Bad problem with the date

Xenoun

Board Regular
Joined
Feb 15, 2011
Messages
63
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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