Date Conversion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to convert a date from 20070407 (yyyymmdd)

to 04/07/2007 can someone help me with this confersion.

Thanks,

Linda
 
Here's one option....

Select your column of "dates"

From the Excel main menu:
<data><text-to-columns>
Click the [Next] button twice
Check: Date.......Select YMD
Click the [Finish] button

or...if you prefer a formula...
A1: 20070407
B1: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
Linda

If it's in A1 use a helper column and copy down, like so

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

(I am presuming US mm/dd/yyyy format, else you're working late)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
 
I need to convert a date from 20070407 (yyyymmdd)
to 04/07/2007 can someone help me with this confersion.
Click to expand...

As long as you are going for a Sting value in the end, this will do what you
want...

Format$(Format$(20070407, "@@@@-@@-@@"), "mm/dd/yyyy")

If, on the other hand, you just want to make this into a pure Date value
(stored dates do not have a "format", they are stored as a Double with the
whole number part representing days from "date zero" and the decimal part
representing the time as a fraction of a day), then this will do that...

CDate(Format$(20070407, "@@@@-@@-@@"))

Rick
 
Here's a formula version of Nick Rothstein's VBA solution:

A1: 20070407
B1: =--TEXT(A1,"0000-00-00")
(format B1 as a date)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Ron Coderre said:
Here's one option....

Select your column of "dates"

From the Excel main menu:
<data><text-to-columns>
Click the [Next] button twice
Check: Date.......Select YMD
Click the [Finish] button

or...if you prefer a formula...
A1: 20070407
B1: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


tenngirl said:
I need to convert a date from 20070407 (yyyymmdd)

to 04/07/2007 can someone help me with this confersion.

Thanks,

Linda
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Which is the best MBOX Converter Tool different formats conversion? 6
Excel date format YYYYMMDD 1
changing date formatted as general number into date format 4
Find then highlight in yellow 6
convert date to different format 4
Spliting Data into Months 1
Delete Duplicate Rows, by Date field 4
For Loop 2

Back
Top