Reformat a Date?

**SueWithQuestion** · 07-27-2012, 07:53 AM

I've been given a file to work with that has date values formatted like this:

Fri Jul 20 12:02:35 EDT 2012

I want them formatted like this (as Date values):

Jul 20 2012

I've tried a number of ways to do this, but can't seem to get it working. Can someone help me?

**benishiryo** · 07-27-2012, 08:28 AM

hi there, i'm assuming your other data has the same format. if you want it to show just as text, you can try:
=MID(A1,5,3)&" "&MID(A1,9,2)&" "&RIGHT(A1,4)

if you need it as an Excel-recognised date, try:
=DATE(RIGHT(A1,4),VLOOKUP(MID(A1,5,3),$G$1:$H$12,2,0),MID(A1,9,2))

then to format as your desired format, right-click & Format Cells -> Custom Format -> mmm dd yyyy

hope that helps

**Pete_UK** · 07-27-2012, 08:37 AM

Originally Posted by benishiryo

... VLOOKUP(MID(A1,5,3),$G$1:$H$12,2,0), ...

You should have pointed out that this would need a table setting up in G1:H12 containing the month abbreviations in column G and the month numbers in column H - actually, you could have just used MATCH on column G and not needed column H.

Hope this helps.

Pete

**benishiryo** · 07-27-2012, 08:41 AM

@Pete_UK

thanks for pointing that out. forgot about that

**SueWithQuestion** · 07-27-2012, 08:51 AM

Thank you VERY much!

I do need it as an Excel date.

I don't understand the $G$1:$H$12 portion of the formula, so I haven't gotten this working yet. What's going on there?

Originally Posted by benishiryo

hi there, i'm assuming your other data has the same format. if you want it to show just as text, you can try:
=MID(A1,5,3)&" "&MID(A1,9,2)&" "&RIGHT(A1,4)

if you need it as an Excel-recognised date, try:
=DATE(RIGHT(A1,4),VLOOKUP(MID(A1,5,3),$G$1:$H$12,2,0),MID(A1,9,2))

then to format as your desired format, right-click & Format Cells -> Custom Format -> mmm dd yyyy

hope that helps

**Pete_UK** · 07-27-2012, 09:17 AM

Originally Posted by SueWithQuestion

I don't understand the $G$1:$H$12 portion of the formula, so I haven't gotten this working yet. What's going on there?

Hi Sue,

my post #3 was aimed as much at you as it was to benishiryo - you need to set up a list of 3-letter months in column G and then put the numbers 1, 2, 3, etc next to them in column H.

OR, you could put his first text-based date formula within DATEVALUE( ).

Hope this helps.

Pete

**benishiryo** · 07-27-2012, 09:18 AM

as Pete mentioned, i actually used that placed month Jan-Dec in G1:G12 to lookup & return a month number placed in H1:H12. you can also use what he suggested. a MATCH formula instead. same thing. put the months Jan-Dec in G1:G12 & do this formula:
=DATE(RIGHT(A1,4),MATCH(MID(A1,5,3),$G$1:$G$12,0),MID(A1,9,2))