lancerxe
asked on
Date format problem in Excel 2002
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?
##### typically means your column is not wide enough to display all characters.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ##########################
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 ##########################
ASKER
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.
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.
ASKER
I got it
thanks barryhoudini
thanks barryhoudini
ASKER
Now how do I copy the formula =TEXT(A1,"00-00-0000")+0
so that all the rows get it
so that all the rows get it
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?
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?
ASKER
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")+ )
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")+ )
ASKER
The macro looks like this:
Sub FormatColumns()
With ActiveSheet
.[B:B].NumberFormat = "mm/dd/yyyy"
.[C:C].NumberFormat = "000"
End With
End Sub
Sub FormatColumns()
With ActiveSheet
.[B:B].NumberFormat = "mm/dd/yyyy"
.[C:C].NumberFormat = "000"
End With
End Sub
ASKER
sorry, that's:
Sub FormatColumns()
With ActiveSheet
.[A:A].NumberFormat = "mm/dd/yyyy"
.[B:B].NumberFormat = "000"
End With
End Sub
Sub FormatColumns()
With ActiveSheet
.[A:A].NumberFormat = "mm/dd/yyyy"
.[B:B].NumberFormat = "000"
End With
End Sub
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
Press F9 to re-calculate or set calculation to Automatic
Tools > Options > Calculation > Automatic
ASKER
Great job barryhoudini!!!
Thanks
Thanks
>>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