Formula to change number into date
 

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


--
anjgoss
------------------------------------------------------------------------
anjgoss's Profile: http://www.excelforum.com/member.php...o&userid=26602
View this thread: http://www.excelforum.com/showthread...hreadid=471111

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.


--
martin0642
------------------------------------------------------------------------
martin0642's Profile: http://www.excelforum.com/member.php...o&userid=27589
View this thread: http://www.excelforum.com/showthread...hreadid=471111

On Tue, 27 Sep 2005 09:18:24 -0500, anjgoss
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 :)


--
anjgoss
------------------------------------------------------------------------
anjgoss's Profile: http://www.excelforum.com/member.php...o&userid=26602
View this thread: http://www.excelforum.com/showthread...hreadid=471111

You're welcome - glad it helped :)


--
martin0642
------------------------------------------------------------------------
martin0642's Profile: http://www.excelforum.com/member.php...o&userid=27589
View this thread: http://www.excelforum.com/showthread...hreadid=471111