>>What's wrong with Excel?

Nothing--those numbers just aren't dates.

Assuming that the first entry is in A1, this formula will convert to a date:

=DATE(MOD(A1,10000),INT(A1

Solved

Posted on 2009-02-20

A column in a spreadsheet contains date of birth info such as these:

10161967

7251978

3131970

6301964

9061954

2161949

10041961

8221941

12131932

3111975

4021926

11061969

7251940

When I tried formatting to mm/dd/yyyy some of the rows appeared as ######

I then checked 1904 date system box under Options\Calculation . Still same problem

All I want is to add / to the dates so they appeared as mm/dd/yyyy

I've spent 3 hours on these already.

What's wrong with Excel?

10161967

7251978

3131970

6301964

9061954

2161949

10041961

8221941

12131932

3111975

4021926

11061969

7251940

When I tried formatting to mm/dd/yyyy some of the rows appeared as ######

I then checked 1904 date system box under Options\Calculation . Still same problem

All I want is to add / to the dates so they appeared as mm/dd/yyyy

I've spent 3 hours on these already.

What's wrong with Excel?

13 Comments

>>What's wrong with Excel?

Nothing--those numbers just aren't dates.

Assuming that the first entry is in A1, this formula will convert to a date:

=DATE(MOD(A1,10000),INT(A1

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

format B1 in required date format, i.e. mm/dd/yy, see attached

regards, barry

date-convert.xls

What do you suggest I do? Should I ask the client to populate dates with /

gke565.

I've expanded the coumn and all it does is more ##########################

I tried your approach but now

this date 10161967 shows as 67/10/16. What I need is 10/16/1967

so we are getting close.

Put the cursor on the bottom right corner of B1 until you see a black +

This is the "fill-handle". Double-click the fill handle and the formula should automatically populate all the way down the column.

Another way is to select the bottom right corner, left click and hold down and just "drag" the formula down as far as you need and release.

If you have a very large number of dates there are other ways to avoid dragging, how many dates are there?

However when I tried the fill-handle all it does is populate the same value of 10/16/1967 whihc is the

=TEXT(A1,"00-00-0000")+ value, even thought the formula says =TEXT(A2,"00-00-0000")+0 or =TEXT(A3,"00-00-0000")+0. I wonder what I'm doing wrong?

so the rows looks like this after the fill handle:

a1 b1

10161967 10/16/1967 (Formula = =TEXT(A1,"00-00-0000")+ )

7251978 10/16/1967 (Formula = =TEXT(A2,"00-00-0000")+ )

3131970 10/16/1967 (Formula = =TEXT(A3,"00-00-0000")+ )

Sub FormatColumns()

With ActiveSheet

.[B:B].NumberFormat = "mm/dd/yyyy"

.[C:C].NumberFormat = "000"

End With

End Sub

Sub FormatColumns()

With ActiveSheet

.[A:A].NumberFormat = "mm/dd/yyyy"

.[B:B].NumberFormat = "000"

End With

End Sub

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!