convert custom to date

I have a column in excel (formatted custom 00000000) which actually represents a date. Is there any easy way to change the format to dd/mm/yyyy? WHen I cut and paste it elsewhere and re-apply the formatting, the dates go all over the place.
LVL 3
pma111Asked:
Who is Participating?
 
regmigrantConnect With a Mentor Commented:
ok, the formula is losing the leading 0 because they are numbers, assuming you don't want to put an apostrophe in front of them all :-

=DATE(RIGHT(TEXT(B31,"00000000"),4),MID(TEXT(B31,"00000000"),3,2),LEFT(TEXT(B31,"00000000"),2))
0
 
regmigrantCommented:
if your 'date' is in a1 and is formatted as (for example) 17022014 (ie: today)
=DATE(RIGHT(a1,4),MID(a1,3,2),LEFT(a1,2)) will give you a formatted date

post an example if that's not what the right interpretation
0
 
pma111Author Commented:
Just tried this, the original date (1st april 2013)

01042013      

returned (with the above formula):

00042531
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pma111Author Commented:
formatted the column dd/mm/yyyy then reapplied the formula, but its still off.

for example

02022014

returns (with the above formula)

20/10/2015
0
 
barry houdiniConnect With a Mentor Commented:
If you use TEXT function like this it will handle single or double digit days

=TEXT(A1,"00-00-0000")+0

Format as dd/mm/yyyy

regards, barry
0
 
regmigrantCommented:
always believe Barry :)
0
All Courses

From novice to tech pro — start learning today.