VBA Date stays as American

Question

I am relatively new to VBA, and I need some help on a code I have been writing. Currently, it looks like this:

Sub RoundedRectangle1_Click()
Selection.NumberFormat = "dd mmm yy"
Range("H2").ClearContents
 Dim Date1 As ValueChange
Range("H2").Value = InputBox("Enter the first date (Monday) of the week you wish to view, in the format DD/MM")
End Sub

As you can see, I have a pop-up box for the user to manually enter the date, but for some reason, once this is entered it keeps providing an answer in the US format, for instance if I type in 04/12, this will appear as "12 Apr 16", rather than "04 Dec 16"

Why not using a [date picker?](http://stackoverflow.com/questions/13409653/how-to-add-date-picker-to-vba-userform) — SBF, Nov 16 '16 at 09:41
@SBF I am unable to download things as it is a work project, and it would be breaking policy — CBooth, Nov 16 '16 at 11:38

score 0 · Answer 1 · answered Nov 16 '16 at 12:01

0

According to my tests, InputBox returns a string. What I would do, is write the following function (just demo, no error handling in this code):

Private Function ParseDate(sInput As String) As Date
    Dim sTmp() As String
    sTmp = Split(sInput, "/")

    ParseDate = DateTime.DateSerial(2016, sTmp(1), sTmp(0))
End Function

and then simply call it like this:

 Dim sResult As String
 sResult = InputBox("Enter the first date (Monday) of the week you wish to view, in the format DD/MM")
 Range("H2").Value = ParseDate(sResult)

answered Nov 16 '16 at 12:01

Robert J.

2,631
8
32
59

Might be worth replacing `2016` in your DateSerial with `Year(Date())` to keep the current year. – Darren Bartrup-Cook Nov 16 '16 at 13:32
of course, but that's the reason why i said that it's just a demo :) – Robert J. Nov 16 '16 at 14:17
Unfortunately not having any luck with that either @DarrenBartrup-Cook - proving a frustrating task.. – CBooth Nov 17 '16 at 09:04

score 0 · Answer 2 · edited Dec 31 '16 at 16:40

This macro, which asks for the date to be printed at the head of an attendance register works for dd/mm/yy or dd/mm in the 21st century. Could easily be adapted to include 20th cent

Sub Print_Register()
'
' Print_Register Macro
Dim MeetingDate, Answer

    Sheets("Register").Select
    Range("A1").Select
GetDate:
    MeetingDate = DateValue(InputBox("Enter the date of the meeting." & Chr(13) & _
    "Note Format" & Chr(13) & "Format DD/MM/YY or DD/MM", "Meeting Date", , 10000, 10000))
    If MeetingDate = "" Then GoTo TheEnd
    If MeetingDate < 36526 Then MeetingDate = MeetingDate + 36525 'If no yy add year 2000
    Range("Current_Meeting_Date") = MeetingDate
    Answer = MsgBox("Date OK?", 3)
    If Answer = 2 Then GoTo TheEnd
    If Answer = 7 Then GoTo GetDate
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
TheEnd:
End Sub

VBA Date stays as American

2 Answers2