VBA CODE TO CONVERT TO PROPER DATE

[NeelsBorstlap]

Hi

I get an excel file from 3rd party with the dates in a column like this:

Total Variable Insurance	First Instalment Date	Final Payment Due Date	Last Receipt Date
0.00	15/02/2021	15/02/2021	15/02/2021
0.00	12/02/2021	12/02/2021	15/02/2021
0.00	19/02/2021	19/04/2021	23/04/2021
0.00	19/02/2021	19/02/2021	19/02/2021

I want to use VBA code that will convert the data in the columns with the dates info in it to look like this correct date format:

15-Jan-21

12-Jan-21

19-Jan-21

19-Jan-21

and another file from someone else with date in a column like this:

Inst.	Cycle	Inst. Dt	Track.	Amount
1	Monthly	2021/12/10 Fri	7 Day	3,167.55
1	Monthly	2021/12/11 Sat	7 Day	8,470.93
2	Monthly	2021/12/12 Sun	7 Day	3,370.61

I want to use VBA code that will convert the data in the columns with the dates info in it to look like this correct date format:

10-Jan-21

11-Jan-21

12-Jan-21

I am not good with code and most of the time only record the macro but can net get this to work.
Hope someone can help me.

 

[jasonb75]

Please see post 8, your mini sheet doesn't provide the necessary information for me to write your code.

 

[SQUIDD]

Your mini sheet is not a true example of your data set.

Without knowing exactly what columns the dates are in means the code cannot be written.

Unless you wanted to say select the column manually , then run the code on the selected column.

 

[BalloutMoe]

This should do the job, please test it out and let me know

Dim Dates As Date
For i = 2 To 5
Dates = CDate(ThisWorkbook.Worksheets(1).Range("A" & i))
NewDates = Format(Dates, "d-mmm-yy")
ThisWorkbook.Worksheets(1).Range("B" & i) = NewDates
Next

 

[jasonb75]

This should do the job, please test it out and let me know

It can be done much simpler and without wasting time looping, we're just waiting for the OP to clarify the correct ranges.

 

[BalloutMoe]

It can be done much simpler and without wasting time looping, we're just waiting for the OP to clarify the correct ranges.

I am sure it can and looking forward to seeing what the better solution as I am trying to learn more and more myself well helping as much as I know.

 

[Dave Patton]

It would be nice to know the Regional Settings etc.
Formulas, Data Text to Columns or VBA can provide the solution. I tested the suggestions; system Regional Settings are dd-mm-yy.
You can custom format the dates Control 1 Numbers Custom and/or edit the VBA.
The 2 subs converted a copy of A2:A5 and C2:C5; see A9:A12 and C9:C12.
Check the list of shortcut keys Apply the Date format using day, month, and year Ctrl-Shift-#

Another way is to copy a blank cell and the edit paste special Add; then format to your preference.

Date and Time.xlsm
	A	B	C	D
1	Before	After	Before	After
2	15-02-21	15-Feb-21	2021/12/10 Fri	10-Dec-21
3	12-02-21	12-Feb-21	2021/12/10 Fri	10-Dec-21
4	19-04-21	19-Apr-21	2021/12/11 Sat	11-Dec-21
5	19-02-21	19-Feb-21	2021/12/10 Fri	10-Dec-21
6
7	VBA
8	ConvertTextToDates11a		ConvertToDate11b()
9	15-Feb-21		10-Dec-21
10	12-Feb-21		10-Dec-21
11	19-Apr-21		11-Dec-21
12	19-Feb-21		10-Dec-21
24a

Cell Formulas
Range	Formula
B2:B5	B2	=A2+0
D2:D5	D2	=--LEFT(C2,10)

Try Data TextToColumns with macro recorder and then edit the code.

Sub ConvertTextToDates11a()
' text like system date 19-02-2021 to dates
' edit format to your preference

With Selection
.NumberFormat = "d-mmm-yy"
.Value = .Value
End With

End Sub

Sub ConvertToDate11b()
' text like 2021/12/10 Fri convert to date
' edit format to your preference

    With Selection
      .TextToColumns DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=1, Tab:=1, _
        Semicolon:=0, Comma:=0, Space:=1, Other:=0, FieldInfo _
        :=Array(Array(1, 5), Array(2, 9)), TrailingMinusNumbers:=1
      .NumberFormat = "d-mmm-yy"
    End With
End Sub