Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB

[Boormo]

Hi,

Reletively new to VBA but learning quickly!!!

I have inherited some sheets which contain data exported from an SAP based system. The data is cool however all dates are exported as a string in the form dd.mm.yyyy. I need to convert these columns(3 columns C,F and I) of "string dates" to date formats dd/mm/yyyy, 3 columns C,F and I.

Am able to do this outside of VBA i.e in the sheets themselves with Formulae etc.. but need to encorporate this conversion in a VBA routine.

Please help, have come un stuck as any attempts seems to return a mixture of dd/mm or mm/dd.

Cheers

[WorldBridge]

This is how you can handle it:

    Dim sDate As String             'variable where your string data is at
    Dim dDate As Date               'date data type for use in commands
    
    dateHolder = sDate
    dDate = CDate(sDate)          'convert sDate to Date data type and assign to variable

'You can also use format to put it into any special format you require.

    dDate = Format(sDate, "Short Date")    'would return '17/04/2012' 
    dDate = Format(sDate, "Long Date")      'would return 'April 17, 2012' 
    dDate = Format(sDate, "yyyy/mm/dd")   'would return '2012/04/17'

[Winon]

@ WorlBridge,

Nice! Short and sweet complete answer.

[WorldBridge]

Thanks! I would not be upset with some positive reputation...

I've had to tackle so many of these problems doing my budget program that I just have to look up the solution in my own code lol

@ WorlBridge,

Nice! Short and sweet complete answer.

[Winon]

Yeah, No harm in doing that for you. Could you also perhaps press a few buttons to return the favour. LOL !

[WorldBridge]

It won't let me. I get a message saying I must spread some reputation around before giving you any more.
You've become too helpful for your own good.

Yeah, No harm in doing that for you. Could you also perhaps press a few buttons to return the favour. LOL !

[Winon]

@ WorldBridge,

Thank you WorldBridge, nice to have you as a friend!

[Boormo]

Thanks worldbridge!, this allows me to achieve exactly what I need. Can see where I was going wrong.

Thanks!

[WorldBridge]

My pleasure.
Please mark the thread as solved by going to Thread tools > Mark this thread as solved
located at the top of your initial post.

And I would appreciate if you clicked the star under my name in my post to you and gave me positive reputation.

Thanks worldbridge!, this allows me to achieve exactly what I need. Can see where I was going wrong.

Thanks!

[Winon]

@ WorldBridge,


Hi WorlBridge,

No need to quote an OP's reply everytime as the Moderators of this Forum do not take kindly to that, especially if it happens to be a bit large.

Keep up the good work,