How can I stop automatic date formatting?
 

I have spreadsheet into which I regularly copy and paste a data table
from a website. The website table shows dates as, for example, Dec 31
and when these are pasted into my spreadsheet they then appear as Dec-31
and if I click on the cell it actually contains 01/12/1931! I presume
this is because of the way Excel (2003) tries to (helpfully!) recognise
what it thinks is a date.

Is there a way to stop this happening please?

Alternatively, is there a way to extract the real date, in this case 31
Dec 2014, from theses cells. I have tried using RIGHT and MID functions
but don't seem to get to the right result.

Thanks.

How can I stop automatic date formatting?
 

Hi,

Am Fri, 2 Jan 2015 23:35:07 -0000 schrieb Victor Delta:

I have spreadsheet into which I regularly copy and paste a data table
from a website. The website table shows dates as, for example, Dec 31
and when these are pasted into my spreadsheet they then appear as Dec-31
and if I click on the cell it actually contains 01/12/1931! I presume
this is because of the way Excel (2003) tries to (helpfully!) recognise
what it thinks is a date.

if you do not need the date for calculations format the column as text
BEFORE inserting the data.
What date do want into the cell? Always the last of month?
You could try:
=DATE(2014,MONTH(A1)+1,0)
Or you use VBA:

Sub DateFormat()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
rngC = DateSerial(2014, Month(rngC) + 1, 0)
rngC.NumberFormat = "MMM DD"
Next
End With
End Sub

If you want the last two digits as day instead year try:
=DATE(2014,MONTH(A1),MOD(YEAR(A1),1900))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How can I stop automatic date formatting?
 

In article , claus_busch@t-
online.de says...

Hi,

Am Fri, 2 Jan 2015 23:35:07 -0000 schrieb Victor Delta:

I have spreadsheet into which I regularly copy and paste a data table
from a website. The website table shows dates as, for example, Dec 31
and when these are pasted into my spreadsheet they then appear as Dec-31
and if I click on the cell it actually contains 01/12/1931! I presume
this is because of the way Excel (2003) tries to (helpfully!) recognise
what it thinks is a date.

if you do not need the date for calculations format the column as text
BEFORE inserting the data.
What date do want into the cell? Always the last of month?
You could try:
=DATE(2014,MONTH(A1)+1,0)
Or you use VBA:

Sub DateFormat()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
rngC = DateSerial(2014, Month(rngC) + 1, 0)
rngC.NumberFormat = "MMM DD"
Next
End With
End Sub

If you want the last two digits as day instead year try:
=DATE(2014,MONTH(A1),MOD(YEAR(A1),1900))


Regards
Claus B.

Many thanks for that.