Changing the format of a cell in Excel

[shcuck]

Well here is the situation. I import data into Excel from Access, which is transfered over as a number. I want to change the format of that cell from number to date. But when I do so, I get all "#################". I have tried to resize the column but still no go. I want to change the format from 20021213 to 12/13/2002. I have tried through the basic format cell... I have also tried to copy and paste values, and I have also created a formula that will Left, Mid and Right the Month, Day and Year, and then join them all in a new cell in the order I need, and then change the format to date, but that did not work either....


What am I doing wrong........?

Please help. I am almost done with a VBA project, and this is the last piece of the puzzle.

Thanks in advance for your help.

Shcuck

 

[DaleWatson123321]

I've tested the following formula and it works...

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

This illustrates one way.
(Depending on your date format you may need to play with it a little.)

Sub test()
  Range("A1") = DBDate("20021213")
End Sub

Function DBDate(DateString As String) As Date
DBDate = DateValue(Mid(DateString, 5, 2) & "/" _
                 & Mid(DateString, 7, 2) & "/" _
                 & Mid(DateString, 1, 4))
End Function

 

[shcuck]

Thanks a lot for you prompt response. And I want to say that both of them work.

Zathras: I would like to use what you wrote, but instead of the "20021213" of the DBDate being a constant, how can I change that to read the value of a cell instead of a set value. For example, I want it to modify cell B2, after it read the value of B2. Just the way you have it, instead of DBDate("20021213&quotbeing a constant value, I want it to read the value from cell b2.

I hope I made sence.

Thanks

 

[Zathras]

If you are not already using a macro to control the process, then Dale's formula is preferable. Assuming your raw data is in column B, then you can populate a new column C with the formula

=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

then hide column B. (or alternately, copy and paste special values back into column B then delete column C)

If you are using a macro to control the process, then something like this might be what you are looking for: (Call the subroutine "ConvertDates" somewhere after you have loaded all of the data. Example assumes dates are in column B starting with row 2.)

===================================================
Sub ConvertDates()
Dim r As Range
Dim c As Range
Set r = Range("B2:B65536&quot
For Each c In r
If c.Text = "" Then
Exit For
Else
c.Value = DBDate(c.Text)
End If
Next c
Set r = Nothing
End Sub

Function DBDate(DateString As String) As Date
DBDate = DateValue(Mid(DateString, 5, 2) & "/" _
& Mid(DateString, 7, 2) & "/" _
& Mid(DateString, 1, 4))
End Function
=====================================================