Convert Date from yyyymmdd to mm/dd/yyyy 2

[net123]

I have a field in Excel in which the date format is in format yyyymmdd and I would like to convert to this format: mm/dd/yyyy.

What is the easiest way to do this?

 

[xlbo]

Do you mean you have a cell that looks like
20030723 ??
If so, use
=value(mid(A1,5,2)&"/"&right(A1,2)&"/"&left(A1,4))
and format as mm/dd/yy (it'll show as the date serial number in general or number format)
where your "date" is in A1 - adjust to suit


Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[DrBowes]

using Worksheet function:

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

(assuming your date is in cell A1.)

Use DateSerial Function if using VBA. Just replace DATE with DateSerial.

 

[net123]

XLBO AND DRBOWES:

Thanks to both of you for your quick responses!! They both work, but I do have one more question: if the cell to be converted is blank (b/c not all of them have dates in them), how can i leave the to_be_converted cell blank as opposed to giving me #VALUE!

Thanks once again!

 

[xlbo]

=if(A1="","",value(mid(A1,5,2)&"/"&right(A1,2)&"/"&left(A1,4)))
will sort out your blanks


Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[net123]

XLBO:

Your if statement didn't work for me. It returned a string value of numbers and not a date.

Are you certain your code is correct?

 

[bryanbayfield]

[tt]=IF(NOT(ISBLANK(A1)),DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"&quot[/tt]

 

[xlbo]

[cough] - think you'll find that dates ARE numbers and if you format the result cell as mm/dd/yyyy (as I wrote in my 1st post), you'll see your date

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[net123]

Sorry guys - don't know what I'm doing wrong. I tried both of you guys functions and they work fine if there are numbers there, but when there is nothing, I get back the #VALUE!

Sorry, but if you can help once more I would greatly apprecaite it.

cell a1 cell a2
text date (mm/dd/yyyy)
20020816 8/16/2002

 

[Jooky68]

Double click on the cell that says #Value. The value should show up. You probably just have to adjust the size of your cell to fit the value. It might not be able to be displayed due to the fact that the cell you are displaying it in is too small.

This help?

 

[xlbo]

Have you actually tried my 2nd posting or Bryan's post ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244