VBA for Input Date

[Anthon]

Hi guys,

I have a little problem. I want to get users to input dates via an input box and the output should be in the format dd-mmm-yy. However using the code below, when a user inputs 13/12/16 the output is 16-Dec-13. It should however, be 13-Dec-16. Any help on this please?


Sub Date()
Dim wb As Workbook, ws As Worksheet
Dim dmy As Date, NR As Long
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
NR = ws.Range("A" & Rows.Count).End(xlUp).Row
NR = NR + 1
dmy = Format(CDate(dmy), "dd-mmm-yy")
dmy = InputBox("Please enter the date", "Hi User")
ws.Cells(NR, 3) = dmy
End Sub

[Paul_Hossler]

Can you change the NumberFormat of the destination cell(s)?



Option Explicit
Sub DateIsReservedWord()
Dim wb As Workbook, ws As Worksheet
Dim dmy As Date, NR As Long

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

NR = ws.Range("A" & Rows.Count).End(xlUp).Row
NR = NR + 1

dmy = InputBox("Please enter the date", "Hi User")

ws.Cells(NR, 3).NumberFormat = "dd-mmm-yy"
ws.Cells(NR, 3).Value = dmy
End Sub

[Anthon]

Hi Paul,

Still not working. I think at the point of the inputbox, the first digits before the "/" is already treated as the year instead of the date, so that 13/12/16 is recognised as 16-Dec-13. I know I can swap 13 with 16 to make everything right, however the purpose of the inputbox is to make it flexible, such that users can key in the date in any format they wish, and it will always output dd-mmm-yy. For eg users can also key in 13-Dec-16.

[Paul_Hossler]

I'm taking a guess and thinking it's different regional Settings, since it works for me using a US m/d/y input

19137
Give this a try



Option Explicit
Sub DateIsReservedWord()
Dim wb As Workbook, ws As Worksheet
Dim dmy As Date, NR As Long

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

NR = ws.Range("A" & Rows.Count).End(xlUp).Row
NR = NR + 1

dmy = InputBox("Please enter the date", "Hi User")

If IsDate(dmy) Then
ws.Cells(NR, 3).NumberFormat = "dd-mmm-yy"
ws.Cells(NR, 3).Value = DateSerial(Year(dmy), Month(dmy), Day(dmy))
End If

End Sub

[Anthon]

Hi Paul,

I tried your example for 12/25/2017 and it works for me too. However, my users would tend to key dd/mm/yy and I would like to see that in output dd-mmm-yy.

[SamT]

the output of an InputBox is a String
dmy is a Date
Declare dmy as a String then manipulate the string after the InputBox

If that doesn't work 100% try a custom InputBox, AKA UserForm.

Are your regional settings Right to Left Text?

At the very least suggest the desired format

dmy = InputBox("Please enter the date in the format dd-mm-yy", "Hi User")

For the best User experience, analyze their input by:
Discover the separator (. , - / " " etc)
Split the inputs
Is one this year, this month?
Do you expect Dates in the future or in the past? How far past or future
Does their input make more sense as mm-dd-yy or dd-mm-yy?

Reassemble the inputted values as reasonably as possible

[Paul_Hossler]

Hi Paul,

I tried your example for 12/25/2017 and it works for me too. However, my users would tend to key dd/mm/yy and I would like to see that in output dd-mmm-yy.

I still think that if a user's Regional settings are not set to dd-mmm-yy is won't work reliably

19138


Give this version a try and see if it's any better



Option Explicit

Sub DateIsReservedWord()
Dim wb As Workbook, ws As Worksheet
Dim dmy As String, NR As Long
Dim v As Variant

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

NR = ws.Range("A" & Rows.Count).End(xlUp).Row
NR = NR + 1

dmy = InputBox("Please enter the date using 'dd-mm-yy' format", "Hi User")

If Len(dmy) = 0 Then Exit Sub

dmy = Replace(dmy, "-", "/")
v = Split(dmy, "/")

If Len(v(2)) = 2 Then v(2) = "20" & v(2)

ws.Cells(NR, 3).NumberFormat = "dd-mmm-yy"
ws.Cells(NR, 3).Value = DateSerial(v(2), v(1), v(0))

End Sub




19139

[mdmackillop]

This uses regional settings to analyse date

Sub Test()
Dim dt, i, sep, Mydate


dt = InputBox("Please enter the date", "Hi User")
For i = 1 To Len(dt)
sep = Mid(dt, i, 1)
If Not sep Like "#" Then Exit For
Next i
dt = Split(dt, sep)
Select Case Application.International(xlDateOrder)
Case 0 'month-day-year
Mydate = DateSerial(dt(2), dt(0), dt(1))
Case 1 'day-month-year
Mydate = DateSerial(dt(2), dt(1), dt(0))
Case 2 'year-month-day
Mydate = DateSerial(dt(0), dt(1), dt(2))
End Select
MsgBox Format(Mydate, "dd-mmm-yy")
End Sub

[Bob Phillips]

Why not just cast it to a date


MyDate = CDate(InputBox("Please enter the date", "Hi User"))

[SamT]

Thanks, md. That went straight to my KB.

[Paul_Hossler]

Based on #1, I'm assuming that the User's PC's Region and the preferred input format are not compatible


However using the code below, when a user inputs 13/12/16 the output is 16-Dec-13. It should however, be 13-Dec-16. Any help on this please?


Personally, I think the Regional Settings Date Format should be changed in the user's PC to use the dd-MMM-yy format (#7) if that's what they want, then a Date is a Date

Using default US Date format M/d/yyyy, md's and xld's macros don't seem to work around the incompatibility




Option Explicit

'when a user inputs 13/12/16 the output is 16-Dec-13.
'It should however, be 13-Dec-16. Any help on this please?

Sub Test2()
Dim dt, i, sep, Mydate

'md
dt = InputBox("Please enter the date", "Hi User") ' 13/12/16
For i = 1 To Len(dt)
sep = Mid(dt, i, 1)
If Not sep Like "#" Then Exit For
Next I
dt = Split(dt, sep)
Select Case Application.International(xlDateOrder)
Case 0 'month-day-year
Mydate = DateSerial(dt(2), dt(0), dt(1))
Case 1 'day-month-year
Mydate = DateSerial(dt(2), dt(1), dt(0))
Case 2 'year-month-day
Mydate = DateSerial(dt(0), dt(1), dt(2))
End Select

MsgBox Format(Mydate, "dd-mmm-yy") '>>>>> 12-Jan-17



'xld
Mydate = CDate(InputBox("Please enter the date", "Hi User")) ' 13/12/16
MsgBox Format(Mydate, "dd-mmm-yy") '>>>>> 16-Dec-13


'ph
Dim v As Variant
Mydate = InputBox("Please enter the date using 'dd-mm-yy' format", "Hi User")

Mydate = Replace(Mydate, "-", "/")
v = Split(Mydate, "/")

If Len(v(2)) = 2 Then v(2) = "20" & v(2)

MsgBox Format(DateSerial(v(2), v(1), v(0)), "dd-mmm-yy") '>>>>> 13-Dec-16

End Sub




PS - I do like md's Application.International(xlDateOrder) -- I wasn't aware of that

[mdmackillop]

@Paul

PS - I do like md's Application.International(xlDateOrder) -- I wasn't aware of that
I can't claim prior knowledge. I found it in looking for a response to this question. I strongly suspect that my code here is a long winded way of saying CDATE, as XLD :hi:has kindly pointed out.
Regards
MD