Here is a bizarre problem sent in by a viewer. Many of you will think that you know the answer, but this problem is not as simple as it appears. In a worksheet, any dates are instantly converted to their 5 digit serial number. The actual date is shown in the formula bar, but the worksheet cells show the underlying serial number. Yes my first thought was that someone had formatted the worksheet using a Number format, but this is not the problem. Hint: this is a simple spreadsheet set up to log information, and there is not a single formula anywhere in the worksheet. Episode 447 solves the mystery.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen Have a great question was sent in today.
If you have a question for the netcast, you can get it to me however you like you leave a voicemail at either of our numbers in the US or London.
If you have Skype, you can call me at billjelen and leave a voicemail there or just send me an e-mail.
This question came in from George on the internet.
George has a question that I've actually run to once before him the first time I saw this.
It completely through me.
He said I have a spreadsheet anytime that I enter a date in a column it changes to a five digit number.
I know you're thinking this is a really easy one that the column is just formatted incorrectly.
Excel stores dates is the number of Days since January 1st, 1900.
So, when you get a number like 39125, that is a date in 2007 you just need to format it as a date.
But, that's not what's happening here.
If I type in control+semicolon it looks like today's date, when I hit Enter it changes back to the number.
What's unusual is in the formula bar, we see that it actually storing the date.
So, you say well, what you have to do is go to format cells I'll select the column format cells and change the Format to a Date format, click OK.
But, strangely enough that doesn't solve the problem.
Now, any time that I've seen this it happens on a spreadsheet, that has no other formulas anywhere in it.
What we're actually in here is, we are in something called Show Formulas Mode.
Normally, if you're in show formulas mode, you know that something's up because you see formulas everywhere.
But, in this particular case there are no formulas in the spreadsheet and the only clue is that excel is showing us the serial number from the date instead of the actual date.
The solution to this is so simple you have to hold down the control key and hit the backwards acset that's called a grub.
In the US keyboards that's actually on the same key as the Tilde Key, right underneath the F1.
So, hit control grub and it takes us out of show formulas mode.
Might have to adjust your column widths and now your date's actually appear as dates.
One of the most bizarre things I've ever seen when I first notice it.
Again, I thought it was simple format the cells but, that didn't solve the problem.
Very easy to get into show formulas mode particularly in the US because control+tab will take you from spreadsheets to spreadsheet, very easy once you're there to accidentally hit the key right above it and turn on show formulas mode.
Hey, thanks for something by.
We'll see you tomorrow for another netcast for MrExcel.
I'm Bill Jelen Have a great question was sent in today.
If you have a question for the netcast, you can get it to me however you like you leave a voicemail at either of our numbers in the US or London.
If you have Skype, you can call me at billjelen and leave a voicemail there or just send me an e-mail.
This question came in from George on the internet.
George has a question that I've actually run to once before him the first time I saw this.
It completely through me.
He said I have a spreadsheet anytime that I enter a date in a column it changes to a five digit number.
I know you're thinking this is a really easy one that the column is just formatted incorrectly.
Excel stores dates is the number of Days since January 1st, 1900.
So, when you get a number like 39125, that is a date in 2007 you just need to format it as a date.
But, that's not what's happening here.
If I type in control+semicolon it looks like today's date, when I hit Enter it changes back to the number.
What's unusual is in the formula bar, we see that it actually storing the date.
So, you say well, what you have to do is go to format cells I'll select the column format cells and change the Format to a Date format, click OK.
But, strangely enough that doesn't solve the problem.
Now, any time that I've seen this it happens on a spreadsheet, that has no other formulas anywhere in it.
What we're actually in here is, we are in something called Show Formulas Mode.
Normally, if you're in show formulas mode, you know that something's up because you see formulas everywhere.
But, in this particular case there are no formulas in the spreadsheet and the only clue is that excel is showing us the serial number from the date instead of the actual date.
The solution to this is so simple you have to hold down the control key and hit the backwards acset that's called a grub.
In the US keyboards that's actually on the same key as the Tilde Key, right underneath the F1.
So, hit control grub and it takes us out of show formulas mode.
Might have to adjust your column widths and now your date's actually appear as dates.
One of the most bizarre things I've ever seen when I first notice it.
Again, I thought it was simple format the cells but, that didn't solve the problem.
Very easy to get into show formulas mode particularly in the US because control+tab will take you from spreadsheets to spreadsheet, very easy once you're there to accidentally hit the key right above it and turn on show formulas mode.
Hey, thanks for something by.
We'll see you tomorrow for another netcast for MrExcel.
