Solved Excel converts any number pasted into cell to date

March 5, 2014 at 06:27:24
Specs: Windows 7
Running office 7. I have one spread sheet created by someone else that when you paste an number from outside of the program (word outlook, etc) it appears as a date based on the nuber of days sinceY2K not the format that is set in the cell. So 18 comes in as January 18, 2000. if there is a decimal it adds a time element. If you paste to the Formula bar it holds the existing foromat. You can change the format afterwards. I have varified that the cell does not have a date format in it and have tried setting different number formats before pasting.

See More: Excel converts any number pasted into cell to date

Report •


✔ Best Answer
March 7, 2014 at 06:59:06
OK...that's weird.

Obviously I can't copy exactly what you are copying, but I was able to duplicate the problem just by copying a number from a Word doc.

The following steps seemed to resolve the issue (for me) so all I can do is suggest is that you try this and let me know if it works.


1 - Open your weird workbook.
2 - Open a new workbook.
3 - Go back to your weird workbook
4 - Right-Click any sheet tab and choose "Select all sheets"
5 - Right-Click again, choose "Move or Copy"
6 - Click "Make a copy"
7 - Use the pull down arrow for the "To book:" field and choose the name of the workbook that you just opened.
8 - Choose Move to End
9 - Click OK
10 - Try your Paste.

I was able to paste the same number from the same Word into the "new" workbook and it did not change to a date. I did not have to reformat the cell that I was pasting into before I did the paste.

I can't explain why this works, but it does, at least for me. I tried it multiple times and it worked everytime. My best guess would be some type of corruption in the original workbook.

Let me know how that works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 5, 2014 at 09:55:56
First thing that come to mind is a Macro.

Any Macros on the sheet?

MIKE

http://www.skeptic.com/


Report •

#2
March 5, 2014 at 09:57:49
While your at it, check to see if there is a Add-In that might be causing the problem.

For a quick way to check both at the same time,
start Execl in Safe Mode:

From your main Windows Screen
Click Start
Click Run

In the Run Box enter: excel.exe /s

The command line switch /s prevents any Macros or Add-In's from running.

You may need the complete path to your Excel executable,
should be something like:

C:\Program Files\Microsoft Office\OFFICE12\excel.exe

Try adding a pasted number and see what happens.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
March 5, 2014 at 10:07:13
Not having a copy of the workbook to play with, it's hard to say exactly what the issue might be. That said, a few things come to mind...

1 - Does the filename end in xlsm or xlsb? If it does, there may be a macro that monitors the cell in question and automatically runs a set of instructions, forcing a particular value into the cell. To see if a macro is involved, Right-Click the sheet tab and choose View Code. If there is a Worksheet_Change macro attached to the sheet, you should be able to see the code in the window that opens.

2 - What happens if you enter a text string in the cell?

3 - Another thing to try - in a backup copy of the workbook - would be to use the Format Painter to copy the format of that cell to a different cell and see if the "new" cell exhibits the same behavior. I'm not sure what that would tell us, but it would add another data point to work with.

4 - A final thing to to try - again in a backup copy of the workbook - would be to physically delete the cell (Right-Click...Delete...Shift cells up) then copy a "working cell" (one that doesn't force a date) and then do an "Right-Click...Insert Copied cells...Shift cells down". If the cell itself is corrupt, that might solve the issue.

Let us know what you find out.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 5, 2014 at 10:19:55
No Macros found in spread sheet

Report •

#5
March 5, 2014 at 10:20:22
Still happens in safe mode.

Report •

#6
March 5, 2014 at 10:36:04
1. Extension is .xlsx and there are no macros
2. Text copied in comes in fine
3. All cells in workbook have this issue not just some and not just one worksheet.
Used format painter to copy format to another workbook and it did not carry the issue to that workbook.
4. Again all cells are this way. Deleted all cell in the sheet and deleted content of the cells still changing the format from number to a date.

Also did a repair on Microsoft office to see if that was it. No change.


Report •

#7
March 5, 2014 at 11:00:41
Try this and see what happens:

Enter this formula: =CELL("format",A1)

Where A1 is an EMPTY cell that has converted your number to a date and see
what it returns.

MIKE

http://www.skeptic.com/


Report •

#8
March 5, 2014 at 11:19:14
after I Enter this formula: =CELL("format",A1)

It should me the proper code for the format that the cell is set to.


Report •

#9
March 5, 2014 at 11:28:30
Interesting It was the correct code and when I pasted in a number it jumped from F2 (0.00) to G but it showe up as a date. and in the format box at the top a date.

Report •

#10
March 5, 2014 at 11:38:13
I do not know if there is a way to "hide" a Macro, DerbyDad03 would probably know better then me, but try checking for Macro using the VB Editor.

On the Ribbon,
Select Developer
Select Visual Basic Editor

When the Editor window open,
on the left side there may be panel
that tells you all the Macro that are loaded,
see if there is anything suspicious.

Something is operating on the cells, it's either a Macro/Add-in or
the sheet itself may be corrupt.

MIKE

http://www.skeptic.com/


Report •

#11
March 5, 2014 at 12:40:55
If the file has an extension of xlxs then there are no macros.

If you would be willing to send the file to me, with all confidential information removed, I would be willing to take a look at it. I'm intrigued!

I can send you an email address via Private Message or you can upload the file to Dropbox and post a link back here.

Let me know if that is something you would like to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#12
March 6, 2014 at 04:48:58
I can send you a scrubed copy. Please provide contact info.

Report •

#13
March 6, 2014 at 05:03:38
Check your Private Messages. I probably won't be able to work on it until this evening, EST.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
March 6, 2014 at 10:58:09
Thanks. it is on it way at this point it driving me crazy.

Report •

#15
March 6, 2014 at 14:08:46
I took a look at your spreadsheet. Let's go over your original post, bit by bit:

Running office 7. I have one spread sheet created by someone else that when you paste an number from outside of the program (word outlook, etc) it appears as a date

If I right-click on any cell in any sheet (except for the Comments sheet) and choose "Format cells", it shows Date - 14-Mar-01. Every cell, even ones that already have text in them. As far as I can tell, only the Comments sheet has cells that are not formatted as Date - 14-Mar-01.

That would explain why all numbers are being converted to Dates. Are you seeing a different format when you check the cells?

based on the nuber of days sinceY2K not the format that is set in the cell. So 18 comes in as January 18, 2000.

No, it is converting them to the number of days since 1900, just as I would expect Excel to do. If I enter 18 in a cell, I see 18-Jan-00 in the cell and 1/18/1900 in the formula bar. 0/0/1900 is day 0 for Excel, 1/1/1900 is day 1, etc.

if there is a decimal it adds a time element.

As I would expect a cell formatted as a Date to do. It will show the time in the formula bar, but not in the cell if it is formatted as Date - 14-Mar-01.

If you paste to the Formula bar it holds the existing foromat.

As expected.

You can change the format afterwards.

As expected.

I have varified that the cell does not have a date format in it

How have you verified this? If I click in the box above the row numbers to select the entire sheet and choose Format Cells, every sheet except for Comments is showing that the entire sheet is formatted as Date - 14-Mar-01.

and have tried setting different number formats before pasting.

Which works just fine. I formatted a cell as Currency before pasting in an 18 and it displayed $18.00 as expected.

As far as I can tell, there is nothing wrong with your workbook, although it is a little odd that every sheet except for one (Comments is entirely formatted as Date - 14-Mar-01.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
March 6, 2014 at 17:03:10
change all cell in a worksheet to number, or dollar or %. Cut and paste from word a number. What happens? it changes the format to a date format. it does not stay in the preset format like it should.

Report •

#17
March 7, 2014 at 06:59:06
✔ Best Answer
OK...that's weird.

Obviously I can't copy exactly what you are copying, but I was able to duplicate the problem just by copying a number from a Word doc.

The following steps seemed to resolve the issue (for me) so all I can do is suggest is that you try this and let me know if it works.


1 - Open your weird workbook.
2 - Open a new workbook.
3 - Go back to your weird workbook
4 - Right-Click any sheet tab and choose "Select all sheets"
5 - Right-Click again, choose "Move or Copy"
6 - Click "Make a copy"
7 - Use the pull down arrow for the "To book:" field and choose the name of the workbook that you just opened.
8 - Choose Move to End
9 - Click OK
10 - Try your Paste.

I was able to paste the same number from the same Word into the "new" workbook and it did not change to a date. I did not have to reformat the cell that I was pasting into before I did the paste.

I can't explain why this works, but it does, at least for me. I tried it multiple times and it worked everytime. My best guess would be some type of corruption in the original workbook.

Let me know how that works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#18
March 10, 2014 at 06:15:18
Worked great. Thanks. Wish I knew what caused the other to go bad.

Report •

#19
March 10, 2014 at 07:53:10
If you still have the corrupt sheet, you could try saving it in a different format.

Try saving the sheet as an XLSB, which is a Binary Format
See if the change forces the formats to behave themselves.

Open the file and see if you can get the formats to change and stay changed.
Then save it back as an XLSX or XLSM or XLS file and see if that cures the corruption.

Not sure it will work, but might be worth the try.

MIKE

http://www.skeptic.com/


Report •

Ask Question