Solved

# Date format problem in Excel 2002

Posted on 2009-02-20
Medium Priority
196 Views
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?
0
Question by:lancerxe

LVL 93

Expert Comment

ID: 23696702
lancerxe said:
>>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/1000000),INT(MOD(A1,1000000)/10000))
0

LVL 3

Expert Comment

ID: 23696717
##### typically means your column is not wide enough to display all characters.
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 23696767
If your data is in A1, as Patrick suggests, you can use this formula in B1 to convert to a date
=TEXT(A1,"00-00-0000")+0
format B1 in required date format, i.e. mm/dd/yy, see attached
regards, barry

date-convert.xls
0

Author Comment

ID: 23696789
mathewspatrick:
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 ###########################.
0

Author Comment

ID: 23696927
thanks barryhoudini
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.

0

Author Comment

ID: 23696966
I got it
thanks barryhoudini
0

Author Comment

ID: 23696994
Now how do I copy the formula =TEXT(A1,"00-00-0000")+0
so that all the rows get it

0

LVL 50

Expert Comment

ID: 23697032
If you have the formula in B1 and need to copy it all the way down that column then, assuming you have no blanks in column A you can use this method.
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?
0

Author Comment

ID: 23697097
there are 958 rows.
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")+ )

0

Author Comment

ID: 23697132
The macro looks like this:
Sub FormatColumns()
With ActiveSheet
.[B:B].NumberFormat = "mm/dd/yyyy"
.[C:C].NumberFormat = "000"
End With
End Sub
0

Author Comment

ID: 23697136
sorry, that's:

Sub FormatColumns()
With ActiveSheet
.[A:A].NumberFormat = "mm/dd/yyyy"
.[B:B].NumberFormat = "000"
End With
End Sub
0

LVL 50

Expert Comment

ID: 23697334
If you get the same value in every cell that may mean you have Calculation set to Manual.
Press F9 to re-calculate or set calculation to Automatic
Tools > Options > Calculation > Automatic
0

Author Closing Comment

ID: 31549445
Great job barryhoudini!!!
Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month15 days, 1 hour left to enroll