?
Solved

Date format problem in Excel 2002

Posted on 2009-02-20
13
Medium Priority
?
196 Views
Last Modified: 2012-05-06
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
Comment
Question by:lancerxe
13 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
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

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

Accepted Solution

by:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:lancerxe
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

by:lancerxe
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

by:lancerxe
ID: 23696966
I got it
thanks barryhoudini
0
 

Author Comment

by:lancerxe
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

by:barry houdini
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

by:lancerxe
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

by:lancerxe
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

by:lancerxe
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

by:barry houdini
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

by:lancerxe
ID: 31549445
Great job barryhoudini!!!
Thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…

839 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question