How to change date format from dd-mm-yyyy to dd-mm-yy in formula bar of MS-EXCEL?

Question

I've written a date in one of the cells of MS Excel spreadsheet as 26-09-2013. Then by right clicking on that cell I selected Format cells and gone to Number->Date option and by selecting the type value as *14-03-01 and changed the date format to DD-MM-YY. Now the date in the cell looks as per the selected date format.

Now, what I want is to use the same date format into formula bar too. But when I select the cell having date in dd-mm-yy the date in formula bar is displayed as dd-mm-yyyy. Can you help me in bringing the date in dd-mm-yy in formula bar too?

Solution 1

In the Formula Bar, a date will show according to your computer's setting for the "Short date".

In Control Panel > Clock, Language and Region > Region and Language you can set these defaults for your computer.

The Excel "short date" format will always be displayed in the formula bar. In the screenshot below, you can see my regional settings. The Excel cell has today's date, formatted as a "Long Date". The formula bar shows the "Short date", which is determined by the computer's regional settings.

So, no, you cannot change the way the date shows in the formula bar, unless you are prepared to change your computer settings for "short date". This will then apply to all applications that use that date format, which may be way more than just Excel.

Solution 2

There is no way to change the date format in the formula bar. Excel actually stores dates as serial number, so your example of September 26, 2013 is actually stored in Excel as 41543. You can use the format cell settings to change the in-cell output to whatever format you need, but there is no explicit "formula bar" format. It is just there as a way to show you the data contained in the cell.

The only way to change the view in the formula bar would be to convert the date to text using the TEXT() function, but then you would no longer be able to interact with the date as a number.

A full explanation of the way dates and times work in Excel is available on Excel Tactics: The Definitive Guide to Using Dates and Times in Excel