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]

You don't need vba for that, simply select your date cells then press Ctrl #

 

[SQUIDD]

Hi

So in your 1st example excel seems to recognise that its a date

so the below seems to work

Columns("C:C").NumberFormat = "dd-mmm-yy"

However, in your second example excel does not recognise the date as a date, and the above only works if you remove the last 4 charracters from the cell.

Someone else may have another suggestion here, but a code could be written to remove these charracters perhaps, but the question is, are there any more scenarios where the data may look different.

Dave

 

[NeelsBorstlap]

You don't need vba for that, simply select your date cells then press Ctrl #

Thanks for you reply.

Not sure what you mean by Ctrl+# - My # is the upper case of 3 - so must I press Ctrl+3 or Ctrl+shift+3

When I select the data and press ctrl+3 the only thing that happens is the format change to italic like this -

15/02/2021

 

[jasonb75]

Not sure what you mean by Ctrl+# - My # is the upper case of 3 - so must I press Ctrl+3 or Ctrl+shift+3

It sounds like you have a US keyboard layout so yes, if it's the upper character on the key then you need to use shift as well.

Ctrl 3 simply toggles italic text on or off.

 

[NeelsBorstlap]

Hi

So in your 1st example excel seems to recognise that its a date

so the below seems to work

Columns("C:C").NumberFormat = "dd-mmm-yy"

However, in your second example excel does not recognise the date as a date, and the above only works if you remove the last 4 charracters from the cell.

Someone else may have another suggestion here, but a code could be written to remove these charracters perhaps, but the question is, are there any more scenarios where the data may look different.

Dave

Dave

Thanks for your reply but that code do not change the data to the proper date format like: from 19/02/2021 to 19-Feb-2021

 

[NeelsBorstlap]

It sounds like you have a US keyboard layout so yes, if it's the upper character on the key then you need to use shift as well.

Ctrl 3 simply toggles italic text on or off.

Hi Jason

The Ctrl+Shift+3 does nothing and the data stay the same.

 

[jasonb75]

In that case your dates are most likely text strings and not real dates as they appear to be in the examples.

What is your default system date format? (the one that you see if you look at the clock in the bottom right corner of your screen).

I also need to know which columns the dates are in on each sheets.

 

[SQUIDD]

Dave

Thanks for your reply but that code do not change the data to the proper date format like: from 19/02/2021 to 19-Feb-2021

Strange. It did for me. But as I said only on your 1st example data set. Not the 2nd.

 

[NeelsBorstlap]

Hi

Here is my data in a minisheet and what it must look like after the vba code is run.

Book2
	A	B	C	D
1	Before	After	Before	After
2	15/02/2021	15-Feb-21	2021/12/10 Fri	10-Dec-21
3	12/02/2021	12-Feb-21	2021/12/10 Fri	10-Dec-21
4	19/04/2021	19-Apr-21	2021/12/11 Sat	11-Dec-21
5	19/02/2021	19-Feb-21	2021/12/10 Fri	10-Dec-21
Sheet1