Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy

I tried going to format cells, then date, then the mm/dd/yyyy format. All I
get a #######. I tried expanding the cells but that didn't work either.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy

It might be that your data is in text format, so reformatting the
cells won't work. Assuming your birthdate is in A1, try this in B1:

=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

Format this cell as mm/dd/yyyy.

Hope this helps.

Pete

On Dec 13, 5:48 pm, Amy Ann <Amy
wrote:
I tried going to format cells, then date, then the mm/dd/yyyy format. All I
get a #######. I tried expanding the cells but that didn't work either.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 287
Default How do I convert a birthdate format from yyyy/mm/dd to mm/dd/y

If dates are text formatted you can convert to true dates this way.

Select column Data Text to columns Next Next under "Column data
format" select "Date" and specify YMD Finish

"Pete_UK" wrote:

It might be that your data is in text format, so reformatting the
cells won't work. Assuming your birthdate is in A1, try this in B1:

=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

Format this cell as mm/dd/yyyy.

Hope this helps.

Pete

On Dec 13, 5:48 pm, Amy Ann <Amy
wrote:
I tried going to format cells, then date, then the mm/dd/yyyy format. All I
get a #######. I tried expanding the cells but that didn't work either.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy

=MID(A1,6,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)

Vaya con Dios,
Chuck, CABGx3



"Amy Ann" wrote:

I tried going to format cells, then date, then the mm/dd/yyyy format. All I
get a #######. I tried expanding the cells but that didn't work either.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format date dd.mm.yyyy to dd/mm/yyyy Kiwi User Excel Discussion (Misc queries) 7 May 7th 23 11:44 AM
change date format from dd/mm/yyyy to mm/yyyy flow23 Excel Discussion (Misc queries) 3 April 4th 23 11:26 AM
convert date mm/dd/yyyy to dd/mm/yyyy maryj Excel Worksheet Functions 2 March 20th 07 08:38 PM
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel Jack Wilson New Users to Excel 4 July 18th 06 01:57 PM
How to convert the dates from the YY:DD forma to MM/dd/YYYY format Sam Excel Worksheet Functions 4 July 15th 05 12:01 PM


All times are GMT +1. The time now is 03:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"