+ Reply to Thread
Results 1 to 5 of 5

Formula to change number into date

  1. #1
    Registered User
    Join Date
    08-24-2005
    Posts
    7

    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

  2. #2
    Registered User
    Join Date
    09-27-2005
    Posts
    6

    Formula for date change

    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.

  3. #3
    Ron Rosenfeld
    Guest

    Re: Formula to change number into date

    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

  4. #4
    Registered User
    Join Date
    08-24-2005
    Posts
    7
    Worked like a charm Martin... thanks very much!

    Ang

  5. #5
    Registered User
    Join Date
    09-27-2005
    Posts
    6
    You're welcome - glad it helped

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1