# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Dates showing as number instead of mm/dd/yy

## jwright650

Something got switched somewhere in my worksheet.

All of my "recertify by" dates are displayed as a number, but when I select it, it has the correct date in it.

I looked in the options menu, but I must be overlooking the switch to change the display back.

----------


## brokenbiscuits

Highlight the column then select 'format cells'>'date'?

----------


## NBVC

You mean you are seeing 5 digit numbers instead?

If so, then it is simply a formatting issue... format those cells (Format|Cells) as Date (from the Number tab).

----------


## brokenbiscuits

I'm guessing it's not that obvious actually, as when selected it's showing the date, which I don't think it would do if it was formatted as a number...

----------


## jwright650

LOL...this is wierd, the cells are already formatted with mm/dd/yy, but they are displaying a number. Every date in the whole workbook is like this. Click on the cell to change it and the date is displayed correctly, click on a different cell and it displays as  a number.

I just check a different workbook and dates are fine over there, I've switched something in just this workbook.

I just attached the workbook to see if anyone can spot the problem.

----------


## NBVC

In Tools|Options, go to View tab and uncheck the Formulas checkbox.

----------


## brokenbiscuits

It's not even the work_book_, but that specific work_sheet_, as when I copy and paste into a new sheet within the workbook the date values appear correctly.

I've never seen this before, I'm out.

----------


## NBVC

Try my last suggestion.. that seems to fix it.. you had it marked to show formulas instead of results....

----------


## jwright650

> In Tools|Options, go to View tab and uncheck the Formulas checkbox.



@NBVC, 

Yup!...that did the trick! Thank you, I was pulling my hair out, I was updating that sheet while certifying and recalibrating tools here at work and somehow I managed to screw that up...

I have an annual Full Two Day Audit of my shop starting on Monday...I didn't need that lil hiccup. whew!

----------


## chetan.ladia

Go to Files->Options->Advance->Under "Display options for this worksheet" section-> Uncheck "show formulae in the sheet instead of calculated result"


This will resolve your problem.

----------


## Chidera

Thanks Chetan.

Two years later and your suggestion saved a life.

----------


## mbettilyon

^^^Sweet, saved me an hour of headache again today, to boot.  thanks!

----------


## TheOracle71

Excellent tip. 
There seems to be keyboard shortcut other than Ctr ~ that triggers a permanent switch for cells to change from results to formula view. I have done this accidentally a few times with my loose fingers on the keyboard. Not sure what the sequence is though. 
Anyway. your tip has worked for me to rectify the issue.
cheers

----------


## Sartoris

Thanks Chetan, very helpful, I was struggling so much!

----------

