Change Date Format shown in Formula Bar

[nagapavan]

Hi,

I want to change the default date format shown in Formula Bar from dd/mm/yyyy to dd-mm-yyyy.

Can you help me with that?

Also, when I try to split a cell containing time in format hh:mm:ss AM / PM, I am unable to split the AM / PM part due to which I am unable to proceed further.

Thanks in advance,
Pavan

 

[anandvarma]

try one of these

Method one

Custom number formatting "dd-mm-yyyy"

method two

=TEXT(A1,"dd-mm-yyyy")

 

[nagapavan]

I am referring to the default date format in the formula bar, not the cell.

Guess I was not clear earlier.

Pavan

 

[Norie]

Pavan

I don't think you can change the format in the formula bar

I'm also not sure the format has anything to do with not being able to split the date/time.

How did you try to split them?

 

[nagapavan]

Norie -

Can't we change the date format in the backend?

I was trying to split using the Text to Columns option which did not work.

Pavan

 

[nagapavan]

Also, I am not held up with splitting due to the date format. But the AM / PM at the end of the date & time format is not whizzing out even if I use convert text to columns and skip the AM / PM column after splitting.

Can you help?

 

[Norie]

How exactly did you try text-to-columns?

 

[nagapavan]

Step 1 - I selected the data
Step 2 - I clicked on Text to Columns option under the Data Menu
Step 3 - I Selected Delimited Option (Next)
Step 4 - I set Space as the Delimiter (Next)
Step 5 - I Selected the column containing AM / PM and selected "Do not Import"
Step 6 - I clicked on Finish

The data format is given below:

4/25/1991 12:00:00 AM

 

[Rick Rothstein]

None of the characters you see in a date/time value are really in the cell. Excel stores dates as a numerical offset from "date zero" (December 31, 1899), so January 1, 1900 is stored as 1 by Excel, January 2, 1900 is stored as 2 and so on up to today (September 19, 2011) which is stored by Excel as 40805. The time part of a date/time value is store as a fraction of a 24-hour day represented by that time. So, 6:00 AM is stored as 0.25 because that time represent 6 hours out of 24 hours (6/24 equals 0.25). So, the current date/time here as I write this is September 19, 2011 2:27:00 AM. Excell stores that as 40805.1020833333. What you see in the cell (and the Formula Bar) is displayed that way as a convenience to the human user... the value in the cell, according to Excel, is 40805.1020833333 (put a date/time value in a cell and then change its Cell Format to General to see this). Anyway, to sum up, the AM you see in the time is not there, at least is is not there as a text value, so Text To Columns does not see it so it can't parse it out.