| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop automatic formatting of cells (number, text, date, etc) | Excel Worksheet Functions | |||
| automatic date formatting | Excel Discussion (Misc queries) | |||
| How can I stop date formatting? | Excel Programming | |||
| import /paste -stop automatic conversion of text 05-12345 to date | Excel Discussion (Misc queries) | |||
| Automatic date formatting | Excel Discussion (Misc queries) | |||
![Victor Delta[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
