Formatting Problems:



1. I have a date field E.g.: January 02, 2011 and i want this Date formatted into 01/02/2011. and when i format the fields using mm/dd/yyyy it gives the exact result. but, when i take this to another program or if i compare it with another excel sheet. the date field is shown as 1/2/2011

2. In another field i need same kind of date format to be converted to Jan-11 (Mmm-yy) but when compared this too gives similar result(1/2/2011) and my comparison shows ERRORS.

is there any way i can solve this.
can i make this permanent or static or something like dat so that it will remain permanent to the format i specify.

Rgds

Xavier

(Note: please check with the fields "D" and "AD" in the attached file)

Attached Images

	1.jpg (12.0 KB, 9 views)
	2.jpg (18.6 KB, 11 views)

Hi Xavier
This is happening because the date formnat in the other program is set differently. For example of your documents are based on different templates they may have differing date formats and this is what is causing the issue.
Hope this helps.
Tony(OTPM)

Hi TONY

Please check with the File attached.

The fields display the desired format. But still the Formula Bar displays the format of Excel (org) maybe and that format displayed on the Formula bar is my problem. (is there anyway dat i can change the format of the formula bar)

Attached Images

	Excel-1.jpg (9.0 KB, 10 views)
	Excel-2.jpg (14.7 KB, 10 views)

Sorry Tony, but no matter how you format the same date in different cells, when you compare these cells with a formula will say they are equal, except the case when one of the cells is formatted as text...
if this is the case here, the problem can be solved converting text in date, with : =DATE(RIGHT(L16;4);MID(L16;4;2); LEFT(L16;2)), where L16 is the cell containing date formatted as text; only now you can correctly compare dates ...

Hi!

Is there anyway i can make the Formula bar display the same
as the fields A1, B1, C1, D1.(PLEASE REFER TO THE ATTACHED EXCEL FILE) i don mind how it is formatted.

E.G: when i click A1 the contents or values in A1 and the Formula bar should be the same. what ever the format or input may be.

Attached Files

PROB.xlsx (13.3 KB, 11 views)

As i guessed, cell A1 was formatted as text, thats why looks different than other cells in formula bar, and excel cannot compare it with other cells containing dates, without the formula posted, to convert text as date. Appears that you formatted cell A1 as mm.dd.yyyy, but the format is not yet applied to the cell... instead of 1 click to cell A1, double click it to open it, then press enter, it will look now the same format as other cells in formula bar .. (Please observe that the value in A1 is allined to the left, as text does, and B1, C1, D1 to the right, as numbers does...) you have to decide: format ALL cells as text, or format ALL cells as date...then they will look the same in formula bar

thanks....

I double clicked (08/06/2005)the field and saw the date exactly the same as in the formula bar (8/6/2005)as you said. but when i pressed Enter key. it again returned back to the same format(08/06/2005).

is there anything i have missed in between.

Attached Files

PROB.xlsx (13.3 KB, 9 views)