change date to text format that really is text

    Hi all,


    I have a macro that connects to Teradata database and with a query pull data into excel.
    we have a set dates (a from date to a too date) that we need to pass as a text to the excel so it will read in the query. So, our dates are like this 20160101.
    Is there a way to use vba to convert all the dates entered to text?


    I tried this, but it jus keep it as dates (when I select a cell and look in the formula bar it shows as date 01/01/2016:


    Code
    Sheet1.Range("B6", "C100").NumberFormat = "yyyymmdd"


    Greetings.

    Re: change date to text format that really is text


    Hi,


    It does the trick, but it change it in the same format it is. I need actually to change it to text and turn the date around. 18/05/2016 should become 20160518


    Maybe in my previous post I did not explain myself good.


    Sorry :(


    Greetings

    Re: change date to text format that really is text


    Try this:

    Code
    With Sheet1.Range("B6", "C100")
    .Value2 = .Worksheet.Evaluate("INDEX(TEXT(" & .Address & ",""'yyyymmdd""),)")
End With

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: change date to text format that really is text


    Either limit the range to the actually populated rows, or use:

    Code
    With Sheet1.Range("B6", "C100")
    .Value2 = .Worksheet.Evaluate("INDEX(IF(" & .Address & "<>"""",TEXT(" & .Address & ",""'yyyymmdd""),""""),)")
End With

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login