Formula to change number into date

A

anjgoss

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. I
there a formula that I can insert that will change it into a date tha
excel will recognize?

Thanks,
An
 
M

martin0642

Hi there - I think this is the same problem I had a while ago. THi
solves it:
This is the formula which reformats into date and puts the dd/mm/yyy
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 t
change them to accomodate the first cell you are trying to change t
whatever column you actually have your dates in.

Also, when you have done the first cell - click and hold the botto
right corner of the cell and drag down to autofill all other cells wit
the formula. Once you have done this - don't forget to copy the entir
column and then "paste special" "Values" back into the column
otherwise you just have formulas in there and as soon as you star
manipulating it it goes screwy!

Hope this helps
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top