Formating Dates
Hi,
I have a list of dates in this format; 20071105 yyyymmdd I would like to have these displayed in excel in a more easy-to-read format, such as dd/mm/yy. If i simply try to format these cells as a date in excel it predictably treats them as negative dates and displays them as ########. Is there a way I can get excel to format these cells correctly, without having to manually type in a huge list of dates? Thanks, Tom |
Formating Dates
A1 = 20071105
B1 = =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Format B1 as Date mm/dd/yy HTH Jim May "Tom Roberts" wrote: Hi, I have a list of dates in this format; 20071105 yyyymmdd I would like to have these displayed in excel in a more easy-to-read format, such as dd/mm/yy. If i simply try to format these cells as a date in excel it predictably treats them as negative dates and displays them as ########. Is there a way I can get excel to format these cells correctly, without having to manually type in a huge list of dates? Thanks, Tom |
Formating Dates
Data/ Text to Columns/ Next/ Next/ Column date format: Date: YMD/ Finish
Format your cell to suit. -- David Biddulph "Tom Roberts" wrote in message ... Hi, I have a list of dates in this format; 20071105 yyyymmdd I would like to have these displayed in excel in a more easy-to-read format, such as dd/mm/yy. If i simply try to format these cells as a date in excel it predictably treats them as negative dates and displays them as ########. Is there a way I can get excel to format these cells correctly, without having to manually type in a huge list of dates? Thanks, Tom |
Formating Dates
Thanks Jim, This worked brilliantly!
"JMay" wrote: A1 = 20071105 B1 = =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Format B1 as Date mm/dd/yy HTH Jim May "Tom Roberts" wrote: Hi, I have a list of dates in this format; 20071105 yyyymmdd I would like to have these displayed in excel in a more easy-to-read format, such as dd/mm/yy. If i simply try to format these cells as a date in excel it predictably treats them as negative dates and displays them as ########. Is there a way I can get excel to format these cells correctly, without having to manually type in a huge list of dates? Thanks, Tom |
All times are GMT +1. The time now is 11:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com