Date Format Issue

[mikemmb]

Hi, I am going mad, worked my way up to some tricky vba, but cannot get a simple date formatted.

What I want to do is use the Forms (Calendar Input Box) for a user to input a date into A1 (formatted as dd/mm/yy). This I have done OK.

Now I want to use that date to create a New Worksheet (in the same workbook) with the worksheet name = Date (but in the format yymmdd).

I cannot get past first base!
If in B1 (formatted custom yymmdd) I put "=A1" it copies the date in dd/mm/yy format.

Tried all sorts of ways including some other solutions (in vba) to similar issues found in the forum, but nothing seems to get past this fundamental hurdle.

I am clearly missing something basic, please put me out of my misery?

Thanks,
Mike

 

[DanD]

I have a similar date transformtion for naming purposes...
BJ46 contains the date of creation which is in DD/MM/YYYY format

Range("BJ46").Select
Selection.NumberFormat = "yyyy-mm-dd;@"

Which outputs as 2008-11-12

HTH

Dan

 

[ColinKJ]

Hi Dan,

You may want to try

Code:
Sub GetDAtes()
Dim Dat As Date
x = InputBox("Enter Date")
If IsDate(x) = False Then
MsgBox ("Invalid Date")
GoTo endsub
End If
D = Mid$(Str$(Day(x)), 2)
M = Mid$(Str$(Month(x)), 2)
Y = Mid$(Str$(Year(x)), 2)
Sheets.Add
ActiveSheet.Name = Y + "-" + M + "-" + D
endsub:
End Sub

Code:

It worked for me

ColinKJ

 

[mikemmb]

Hi Guys,
Thanks for your replies, but not sure if I explained the issue right.

I need the sheetname to be "yymmdd" not "yyyy-mm-dd".

Colin, I have tried changing your:
ActiveSheet.Name = Y + "-" + M + "-" + D to ActiveSheet.Name = Y + M + D
But it drops the month leading zero's and gives a 4 digit year.
So 10 April 68 becomes 1968410 rather than 680410.

Any other clues guys.

Thanks,
Mike

 

[ColinKJ]

Hi Mike,

Try this one then

Code:

Sub GetDAtes()
Dim Dat As Date
x = InputBox("Enter Date")
If IsDate(x) = False Then
MsgBox ("Invalid Date")
GoTo endsub
End If
D = Mid$(Str$(Day(x)), 2)
If Len(D) < 2 Then D = "0" + D
M = Mid$(Str$(Month(x)), 2)
If Len(M) < 2 Then M = "0" + M
Y = Mid$(Str$(Year(x)), 4)
Sheets.Add
ActiveSheet.Name = Y + M + D
endsub:
End Sub

Code:

ColinKJ

 

[mikemmb]

Colin, Spot On, worked straight out of the box.

Thanks a Lot,
Mike