Date Function: How to convert MM-DD-YYYY to DD-MM-YYYY

[ramki]

Hi,

Have for two question in date function

1. How to convert MM-DD-YYYY to DD-MM-YYYY & wise-versa in date function?

2. How to convert a Text Format to Date Format?

Please Help. Thanks in advance

[royUK]

You choose or create a Custom Number format. From the Format menu choose Cells>-Number>-Custom.

How are your dates input as Text?

[pboost1]

One way that I found is to right click the cell, go to format cells, then click date from the list given. The date option then allows you to choose various styles of date formatting.

If you don't like any of the styles there, click custom, then you can type it mm-dd-yy or yy-dd-mm or dd-mm-yy. I hope that helps.

[darkyam]

If your dates appear as a date form recognizable to Excel, but are still being treated as text, put a 0 in a cell, copy that cell, then highlight all the dates, Paste Special -> Add. This should convert them all to actual dates.

[mudraker]

Another method of converting dates that are treated as text

Select Dates
Data Menu
Text To Column
Delimited - Untick all options (Can also use Fixed Width)
Next
Select Date Option
Finish

[legaai]

Years late, but thank you! And the fact that you from from Melbs make it even better ;-) Thanks for saving me countless hours of work with something so simple!

[gak67]

I know you have already found a solution, but somebody else may find this while searching for a solution. You can convert a text date to a date value with the DATEVALUE function. This wont change MM/DD/YYYY to DD/MM/YYYY though and if your default is DD/MM/YYYY it will show, for example May 3 2013 (displayed as text as 05/03/2013), as 5 March 2013. There is another more complicated formula you could use though. If the text date is in cell A1 you could use this formula: =DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,FIND("/",A1)-1)),VALUE(MID(A1,FIND("/",A1)+1,2))). This of course assumes the text date is in the D/MM/YYYY format. It can be altered to match other formats, eg replacing "/" with "-" if necessary.

[FDibbins]

Thanks for the update, gak, and welcome to the forum