Date format issue shows as Jan 8, 2015 (Non date format) but want DD/MM/YYYY - Help

[seash]

Hi all,

Got the above issue,

Date in report appears as Jan 8, 2015 non date format, but I want DD/MM/YYY to allow me to use excel to count days and perform other tasks.

I have tried a few things I know, and also have a method that should get me there, but its a bit long winded and hoped for something a little more swift!

thanks in advance.

[XOR LX]

Hi.

Are the dates in question always of the form either:

mmm d, yyyy

or:

mmm dd, yyyy

?

Regards

[seash]

Hi.

Are the dates in question always of the form either:

mmm d, yyyy

or:

mmm dd, yyyy

?

Regards

Yes they are always either of the 2 formats you have shown.

Thanks

[Special-K]

This works

=(SUBSTITUTE(LEFT(REPLACE(A1,1,4,""),3),",","")+0&"/"&MONTH("1/"&LEFT(A1,3))&"/"&RIGHT(A1,4))+0

format as date

[opusarlo]

um...maybe I am missing something here, but why can't you just format the cell(s) in question? If you right click on the cell you can then click on "format cell". Then you simply scroll down to date and choose the date setting you would like to see. If that does not work you can scroll further down to "Custom" and choose something like "00.\/00.\/0000". There will be some option in your custom format issue that look like that. Between those 2 methods you should be able to get it done. Also, if you format the cell to display a number it will automatically display the numerical value of the date. Something like "42120" depending on the date. This is the number you will need to calculate values between dates. There are several other ways, but at this point I think i might be missing something.