asked on

how to fill in zeros in a text date

I am trying to automate this entire process without having to look it over.
I have an imported table from excel we'll call XLS_import that I have automated to import by button.
It has several date fields and like always the dates come in many formats from the user.
What I need to do is clean them up in a query.
I need the date to stay a text field.
I would like to say in the query if len<10 then add the right amount of zeros.
so that
1/1/2014 would end up 01/01/2014
0r 1/12/2014 would end up 01/12/2014.
Any help would be apprieciated

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

duncanb7

Take a look for Format() function manual from MS at http://msdn.microsoft.com/en-us/library/office/gg251755(v=office.15).aspx

you can do this, probably you will put the date string into Excel cell
such as at Cell A1

Duncan

Sub test()
Range("A2") = Format(DateValue(Range("A1")), "dd/mm/yyyy")
Range("A2").NumberFormat = ("dd/mm/yyyy")
End Sub

Stephen Roesner

ASKER

cool at first I typed it in and it didnt work butthen I went thru the formula builder to see if I was missing anything and then it worked. Thanks a lot. I also had to add for is null so the formular ended up
IIf(IsNull([Date],[Date],Format(DateValue([Date]),"mm/dd/yyyy")) and it works perfect!
Thank you so much.

Stephen Roesner

ASKER

Thanks easy and small always the perfect code.