Excel format date as dd/mm/yyyy

[scr5jo]

Hi Guys,

I used to be able to format a date in excel as dd/mm/yyyy
or yyyy/mm/dd

Now since excel 2016, I get an error that says
"Excel cannot use the number format you typed.
Try using one of the built-in number formats"

How do I get around this, the default dates in excel are useless
thanks!

[kev_]

That is rather odd
What you say you are doing sounds correct and works for me with Excel 2016
Perhaps you are making a very small mistake

This is what I do (and yyyy/mm/dd - also works ok)
right click \ format cells \ (in the Number tab) click Custom \ and enter dd/mm/yyyy (belowType)

Incidentally - dd/yy/yyyy is one of the defaults available for selection under Date and you should be able to use that without creating your own custom format

[scr5jo]

HI Kev,

thanks for the answers, and this is exactly what I'm also doing.
The only thing is the option is not avaiable in the default dates, and it won't accept my custom date.

It's been bugging me for months, and I don't know what's wrong!

[romperstomper]

What are your regional settings in Windows?

If you enter a date in Excel, then select one of the built in date formats, and then switch to custom, what format code does it show?

[kev_]

To my knowledge there are no general settings which would cause the behaviour described
- are there any add-ins that are loaded automatically?
- are you able to use all the other NumberFormat options ?

I would re-install Excel to see if that sorts it

You are probably aware that you can use =TEXT to display dates as you please
(returns text and so cannot be manipulated like dates)
=TEXT(A2,"DD/MM/YYYY")