Unable to format imported cells to a date

[Will S]

Hello all,

I've got an issue where I've imported a table from a different workbook and all the dates that have been imported are showing the number value of the date (even though the original data is formatted to a date) it also won't let me change the format to a date.

It says it's a date in the tool bar at the top, yet refuses to convert over, I've tried editing the cell while it's like this and it still doesn't work. I've looked up similar issues people have had but none seem to be working. Can anyone shed some light on this as I am stumped.

Thanks,
Will S

 

[btomjack]

I've had an issue like this before. Follow this link and see if it helps:
Disable Formula Updates in Excel 2010 - Solve Your Tech

That link shows you how to turn updating off; my guess is that you will actually have to turn it back on. I can't say with any certainty that this will solve your problem, but it's worth a try.

 

[Will S]

That doesn't seem to work, I'm not sure the issue is to do with it resetting as even when it gives the small preview of what the cell will look like as you're selecting the format it doesn't change. it's like it doesn't actually realise that it's a number that can be entered as a date. I've tried linking it with formulas saying a cell "=I15" but still can't format that cell I've even tried copy and paste values then formatting that cell, still doesn't work. It will let me change the dates of hand entered entries though. The whole thing is confusing. Is there any VBA code for excel to read whats in the cell and retype it without actually copying it or any formula like Date(....) that will convert 41739 into a date? (There is actually more than just that one date)

Thanks for trying anyway.

 

[btomjack]

Is there a little green triangle in the upper left corner of the cell?

Maybe you could try this:
=TEXT(I15,"MM/DD/YYYY")

 

[Will S]

Thank you for the help, there was no green triangle but I managed to find the issue in the end... kind of.

In the other posts I've been looking up, it's been saying that if you edit the cell by double clicking -even if you change nothing- it somehow fixes it. I tried it originally but didn't work, turns out I needed to do it with the original cells I was importing from, I'm a bit curious on if things will screw up again so I've made a note of that formula just in case but as only doing the fix to a few cells fixed all of them I'm hoping it's set that as some sort of default.

Thanks again!

 

[btomjack]

I have had that issue with some imports as well. The formula I gave above usually fixes it for me.

Also, you can use F2 instead of double-clicking.

 

[Will S]

Right, the error came back so I've tried that formula and it's working like a charm. Thank you for all the help.