Date help in excel

**ScabbyDog** · 10-24-2013, 04:43 PM

In column B the format is Custom and dd/mm/yyyy. The cell appears in that format which is great - for example today it shows as 24/10/2013. However, when I click the cell and look at the formula bar it displays as mm/dd/yyyy. How can I fix this so that it shows as dd/mm/yyyy in the formula bar as well?

**Ace_XL** · 10-24-2013, 04:59 PM

The cell format is merely cosmetic and for display purposes only. Excel stores the value as a number (date) in mm/dd/yyyy format which is what reflects in the formula bar

If you change this to 24/10/2013 in the formula bar, the cell will not return a date per se (it wont recognize it as a numerical value) and you will not be able to use this date for further calculations if any. Are you ok with that?

**ScabbyDog** · 10-24-2013, 05:02 PM

Only way around it then is to use a helper column which would have =TEXT(A1,""dd/mm/yyyy")
I then would copy the helpercolumn and paste the values into column A and then format column A to date dd/mm/yyyy. It works. Is there not an easier way surely though?