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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

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

Well-known Member
Joined
Dec 29, 2002
Messages
529
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

Board Regular
Joined
Feb 15, 2011
Messages
63

ADVERTISEMENT

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.
 

Xenoun

Board Regular
Joined
Feb 15, 2011
Messages
63

ADVERTISEMENT

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

Well-known Member
Joined
Dec 29, 2002
Messages
529
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

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

Well-known Member
Joined
Dec 29, 2002
Messages
529
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 :)
 

Forum statistics

Threads
1,141,019
Messages
5,703,774
Members
421,315
Latest member
awaisnazir139

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
Top