Link to home
Start Free TrialLog in
Avatar of bill201
bill201

asked on

macro on microsoft excel to convert the entire columns from text to date with datevalue function

my bank statements that i download from the internet,  has always some columns with dates that are not recognised in excel to be valid dates how can i make a macro that will change a few  columns from text to date with datevalue function?
Avatar of Wilder1626
Wilder1626
Flag of Canada image

Hi bill201,

Can you tell us in what format you are getting the dates when you pull it from your bank statement?
What does it look like?
Avatar of bill201
bill201

ASKER

they look like dates but when i try to convert to a different kind of date format excel didn't recognise the dates, so i think that are recognised like plain numbers.
can you type me an example?
Avatar of bill201

ASKER

there is an an example on the excel file.Book1.xlsx
SOLUTION
Avatar of Wilder1626
Wilder1626
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
use datevalue(range) and then format as date
Q-28627123.xlsx
Do you need to override the cells with the new format or you can use the datevalue(range) formula in cell just beside?

I have updated the macro. Had a typo
Private Sub FixDate_Click()
Dim c As Long, LastRow As Long
LastRow = Range("A1").End(xlDown).Row
For c = LastRow To 1 Step -1
    If Cells(c, "A") > "" Then
        Cells(c, "A") = Left(Cells(c, "A"), 2) & "/" & Mid(Cells(c, "A"), 4, 2) & "/" & Right(Cells(c, "A"), 4)
         Cells(c, "A").Value = Format(Cells(c, "A").Value, "dd/mm/yyyy")
    End If
Next
End Sub

Open in new window

Avatar of bill201

ASKER

i tried to use a macro with this code but it's don't do any thing, maybe i don't do it right. can you please add this macro to the excel file that i uploaded.

thanks a lot
In what format does the bank export the file?

You may be bale to resolve the issue as the file is imported.

The dates are coming through as text so I am assuming the download is either .csv or .txt.

When downloading the file, choose the save option rather than Open. Then go to the saved file and open it, you should get the Text Import Wizard come up. One of the steps you can specify the format of the columns as they are imported, one of the options is Date.

Thanks
Rob H
Avatar of bill201

ASKER

thanks alot for your comment and sorry for the  delay i wasn't  in town.

i'm not download direct the file i'm download the data from the bank account with some software and then in that software is an option to export the data, and it's open the data on an normal excel file like i upload it.
Avatar of bill201

ASKER

I've requested that this question be deleted for the following reason:

i don't get an answer, and it's not very important for me to get a solution.
Whether it is important or not to get a solution, you have received answers to the question posed. If they are not suitable for your scenario then maybe you need to expand on the information supplied.

Being cynical, but in no way suggesting that you would, how are we to know that you are not using the solution(s) provided but cancelling the question so it has not "cost" you anything??

As I said earlier, and looking at the file you supplied, the dates are coming in as text. Therefore, as you have said in your question, you can use the DATEVALUE function to convert to proper dates. The result of the DATEVALUE function can either be copied and pasted as values over the original cells or if needed for onward calculations refer to the new column or use the DATEVALUE function within the onward calculation to convert in one step.

DATEVALUE will only recognise the date in your own regional settings. For example, I am in the UK so a text value of 12/25/15 does not recognised as 25 December because it is expecting it to be in format dd/mm/yy but a text value of 25/12/15 does get correctly converted.

David Johnson gave the syntax for the function last week but slight correction/addition, the range should only be a single cell.

Thanks
Rob H
Avatar of bill201

ASKER

I don't got an answer to my question, so after a few days I thought that there is no point to continue the discuss. when it's not so important. just to be clear i have no problem to give points, it's don't costing me anything.

i'm know about datevalue,please look on my question, my question is very simple how to write a "macro" that  changes a few columns from text to date with datevalue function.  Jean-Marc (Wilder 1626) macro doesn't work.

happily i will give you the points. but please solve this question.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Further investigation with different combinations:

Line 6 without DateValue just creates a text string
Line 6 with DateValue creates correctly formatted date

Line 7 if left in converts correct date in line 6 with DateValue back to text
Line 7 commented out does nothing so dates are correct if cells already formatted as Date.

Thanks
Rob H
Avatar of bill201

ASKER

thanks a lot, And apologizes...