Date Serial

H

harpscardiff

Hi,
a1 to a30 contains a date, in the following format YYYYMMDD
I need to convert it into a generic format: DD/MM/YYYY

I'm using the mid function
=MID(A1,7,2)&"/"&MID(A1,5,2)&"/"&MID(A1,1,4)



i've tried:
=DATE(YEAR(EDATE(A1,2)),MONTH(EDATE(A1,2)),DAY(EDATE(A1,2)))
Not working, Keep gettin #Num!

Is there an easier way, so that is is a date format, rather than text?

Cheer
 
D

Dave Peterson

If it really contains a date, then you should be able to just reformat that
cell.
 
R

Ron Rosenfeld

Hi,
a1 to a30 contains a date, in the following format YYYYMMDD
I need to convert it into a generic format: DD/MM/YYYY

I'm using the mid function
=MID(A1,7,2)&"/"&MID(A1,5,2)&"/"&MID(A1,1,4)



i've tried:
=DATE(YEAR(EDATE(A1,2)),MONTH(EDATE(A1,2)),DAY(EDATE(A1,2)))
Not working, Keep gettin #Num!

Is there an easier way, so that is is a date format, rather than text?

Cheers
Click to expand...

1. If the value is an Excel date, then merely change the format
(Format/Cells/Number/Date and select the one you wish; or Custom Type:
dd/mm/yyyy

2. If the value is a number, then you can use this formula:

=DATE(INT(A1/10^4),MOD(INT(A1/100),100),MOD(A1,100))

and format as dd/mm/yyyy

3. If the value is text that looks like a number, method 2 should still work,
unless there are non-printing characters mixed in, in which case you'll have to
strip them out.

4. You can also use the Data/Text-to-columns wizard.
Select the cell
Next
Next
Date: YMD
Finish


--ron
 
Top