Cell formatted as a date won't allow me to format it

TigersLions

New Member
Joined
Nov 15, 2012
Messages
12
Hi Everyone,
I usually just google questions like these and 9 times out of 10, I usually figure out the solution. This one has me stumped. Unlike the solutions I found on the internet which assume the cell is formatted as text, this cell is formatted as a date. Yet, when I try to format it as a short date MM/DD/YYYY like in the other cells, it has no effect on the cell. I have tried custom formats and checking to see if the cell was locked somehow. Not an ideal solution, but I've also tried pasting as a value in a new cell and also doing a datevalue function in a new cell. Nothing is working. I'm also not getting the little sign in the cell that indicates the formatting is different from other cells in that column. They are all showing up as a "date" format. I'm running Excel 2010. Any ideas? Thanks!!!

061814_Excel_Question.jpg
[/URL] click image upload[/IMG]
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
It can show that the cell is set up as a date, but still be a text value. Have you tried copying a blank cell and paste special values + add to see if that repairs your issue?
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Alright then, are you certain there are no leading or trailing blank characters in the cell? Perhaps see if using datevalue will change it to a date for you.
 

TigersLions

New Member
Joined
Nov 15, 2012
Messages
12

ADVERTISEMENT

Alright then, are you certain there are no leading or trailing blank characters in the cell?

Yes. I clicked around in the cell and also did a TRIM() formula and then tried to format as date. This is a tough one!
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
There is only so much I can think of without having access to the issue itself. Perhaps you could download a file to dropbox with only the date column with the issue. Also, did you try the datevalue function as I mentioned before?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

What does =ISNUMBER(J191) give you?
 

Forum statistics

Threads
1,136,765
Messages
5,677,618
Members
419,707
Latest member
Anna vib

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