I am trying to insert a VBA function that requires a date to run.
My dates are being pulled into a data sheet in the 20050301 format. Is there a formula that I can insert that will change it into a date that excel will recognize?
Thanks,
Ang
I am trying to insert a VBA function that requires a date to run.
My dates are being pulled into a data sheet in the 20050301 format. Is there a formula that I can insert that will change it into a date that excel will recognize?
Thanks,
Ang
Hi there - I think this is the same problem I had a while ago. THis solves it:
This is the formula which reformats into date and puts the dd/mm/yyyy the correct way...
=DATE(MID(C3,1,4),MID(C3,5,2),MID(C3,7,2))
The "C" values refer to the cell address so obviously you will need to change them to accomodate the first cell you are trying to change to whatever column you actually have your dates in.
Also, when you have done the first cell - click and hold the bottom right corner of the cell and drag down to autofill all other cells with the formula. Once you have done this - don't forget to copy the entire column and then "paste special" "Values" back into the column - otherwise you just have formulas in there and as soon as you start manipulating it it goes screwy!
Hope this helps.
On Tue, 27 Sep 2005 09:18:24 -0500, anjgoss
<[email protected]> wrote:
>
>I am trying to insert a VBA function that requires a date to run.
>
>My dates are being pulled into a data sheet in the 20050301 format. Is
>there a formula that I can insert that will change it into a date that
>excel will recognize?
>
>Thanks,
>Ang
If you are doing the conversion of your string in VBA, then:
Sub foo()
Const dt = 20050930
Debug.Print DateSerial(Left(dt, 4), Mid(dt, 5, 2), Right(dt, 2))
End Sub
It will work regardless of whether dt is a number or a string.
--ron
Worked like a charm Martin... thanks very much!
Ang
You're welcome - glad it helped
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks