Using VBA to post a date in YYMMDD format.

[TMUAT]

Hi all,

I have seen many posts about changing a date from DD/MM/YY format to YYMMDD format; however, there has been a number of solutions and i have tried to implement some but to no avail.

I want to paste into a format with no "/"s, just YYMMDD and for example, if it was 12/09/17 it would come out with 170912.With a 0 before the 9.

[FONT=Menlo][COLOR=#011993]Sub[/COLOR] Copy1()[/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]'Define Workbooks[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Dim[/COLOR] ActiveWbk [COLOR=#011993]As[/COLOR] Workbook[/FONT]
[FONT=Menlo][COLOR=#011993]Dim[/COLOR] ClosedWbk1 [COLOR=#011993]As[/COLOR] Workbook[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Dim[/COLOR] DateRef1 [COLOR=#011993]As[/COLOR] [COLOR=#011993]Date[/COLOR][/FONT]
[FONT=Menlo][COLOR=#011993]Dim[/COLOR] DateRef2 [COLOR=#011993]As[/COLOR] [COLOR=#011993]Date[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Application.ScreenUpdating = [COLOR=#011993]False[/COLOR][/FONT]
[FONT=Menlo]Application.DisplayAlerts = [COLOR=#011993]False[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Set[/COLOR] ActiveWbk = ActiveWorkbook[/FONT]
[FONT=Menlo][COLOR=#011993]Set[/COLOR] ClosedWbk1 = Workbooks.Open("/Users/A3/Downloads/1.xls")[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]DateRef1 = ClosedWbk1.Sheets("Workings").Range("I3")[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]ActiveWbk.Activate[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Sheets("Team Sheet Workings").Select[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Range("C3") = DateRef1[/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]'Second Team[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]DateRef2 = ClosedWbk1.Sheets("TeamOverview").Range("M2")[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]ActiveWbk.Activate[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Sheets("Team Sheet Workings").Select[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Range("D3") = DateRef2[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]ClosedWbk1.Close [COLOR=#011993]False[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Range("C2").Select[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Application.ScreenUpdating = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]Application.DisplayAlerts = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Sub[/FONT][/COLOR]

The date in the previous document has been pulled out of a much longer code using the =RIGHT(MatchStats2!A1,8) but is formatted as a date.

Any help would be greatly appreciated.

Many thanks,
Tom

 

[kalak]

Do you mean something like

Range(as defined by you).NumberFormat = "DD MM YY"

 or

Range(as defined by you).NumberFormat = "YYYYMMDD"

or

Range(as defined by you).NumberFormat = "MM/DD/YYYY"

or however you want it?

 

[TMUAT]

Hi Kalak,

Thanks for your response but I cannot get this to work in my document.

It also, means that i get some dates as 16917 as opposed to 160917.

Cheers

 

[TMUAT]

Okay, I have now got it working. However, I need to CONCATENATE it with some words and when this happens it does not pull through the date in this format, rather the date number?

 

[steve the fish]

Not sure i totally understand but maybe:

With Range("A1")
    .NumberFormat = "General"
    .Value = Format(.Value, "YYMMDD")
End With

 

[steve the fish]

Or even:

Range("A1").NumberFormat = "yymmdd"

 

[Akuini]

Okay, I have now got it working. However, I need to CONCATENATE it with some words and when this happens it does not pull through the date in this format, rather the date number?

I don't understand why you need CONCATENATE it with some words.
Both answer from kalak & steve work for me.
Your code should look like this:

Range("D3").NumberFormat = "YYMMDD"
Range("D3") = DateRef2