Hello, I have a spreadsheet containing dates in this format, using today, Friday May 2nd, 2008 as an example: 05/02 F I wish to change all of these to this format: May 2, Fri How do I do this?! Thanks!!
Eric, Use the custom format mmm dd, ddd Mike "Eric" wrote: > Hello, > > I have a spreadsheet containing dates in this format, using today, Friday > May 2nd, 2008 as an example: > > 05/02 F > > I wish to change all of these to this format: > > May 2, Fri > > How do I do this?! > > Thanks!!
Additionally, if you don't want the month truncated to 3 letters use mmmm dd, ddd Mike "Eric" wrote: > Hello, > > I have a spreadsheet containing dates in this format, using today, Friday > May 2nd, 2008 as an example: > > 05/02 F > > I wish to change all of these to this format: > > May 2, Fri > > How do I do this?! > > Thanks!!
Your existing date appears to just be text and not actually an XL date. Assuming that to be the case there are a couple of options. 1 is to convert your text to an actual date and then format the date. The other is to convert your text to a date and then back to text... Here is how to get the date =DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))) You can format that date using a custom format of "Mmm d, ddd" Here is how you get the text =TEXT(DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))), "Mmm d, ddd") -- HTH... Jim Thomlinson "Eric" wrote: > Hello, > > I have a spreadsheet containing dates in this format, using today, Friday > May 2nd, 2008 as an example: > > 05/02 F > > I wish to change all of these to this format: > > May 2, Fri > > How do I do this?! > > Thanks!!
Thank you! You're right, the person who created the file just typed in the date like that, it wasn't formatted a certain way. I appreciate you clearing it up. "Jim Thomlinson" wrote: > Your existing date appears to just be text and not actually an XL date. > Assuming that to be the case there are a couple of options. 1 is to convert > your text to an actual date and then format the date. The other is to convert > your text to a date and then back to text... > > Here is how to get the date > =DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))) > You can format that date using a custom format of "Mmm d, ddd" > > Here is how you get the text > =TEXT(DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))), "Mmm d, ddd") > -- > HTH... > > Jim Thomlinson > > > "Eric" wrote: > > > Hello, > > > > I have a spreadsheet containing dates in this format, using today, Friday > > May 2nd, 2008 as an example: > > > > 05/02 F > > > > I wish to change all of these to this format: > > > > May 2, Fri > > > > How do I do this?! > > > > Thanks!!
Here are some formulas that are a little bit shorter... > Here is how to get the date > =DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))) > You can format that date using a custom format of "Mmm d, ddd" =--(LEFT(A2,5)&"/2008") > Here is how you get the text > =TEXT(DATE(2008,VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))), "Mmm d, ddd") =TEXT(--(LEFT(A2,5)&"/2008"),"mmm d, ddd") Rick
