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?
bill201Asked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
I have made an amendment to Jean's code which now works:
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") = DateValue(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

Note the inclusion of <> on line 5 and DateValue on line 6 and I have commented out line 7; if the cells are formatted as date anyway this is not required and maybe was creating the error.

Corrected file attached.

Thanks
Rob H
Date-fix.xlsm
0
 
Wilder1626Commented:
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?
0
 
bill201Author Commented:
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.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Wilder1626Commented:
can you type me an example?
0
 
bill201Author Commented:
there is an an example on the excel file.Book1.xlsx
0
 
Wilder1626Commented:
Can you try like this, see it it will resolved you issue?

Dim c As Long, LR 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

Open in new window

0
 
David Johnson, CD, MVPOwnerCommented:
use datevalue(range) and then format as date
Q-28627123.xlsx
0
 
Wilder1626Commented:
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

0
 
bill201Author Commented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
bill201Author Commented:
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.
0
 
bill201Author Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
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
0
 
bill201Author Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
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
0
 
bill201Author Commented:
thanks a lot, And apologizes...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.