• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

date format from input box

daterange = InputBox("Input date range", "Input DateRange")
    Range("T1").Value = daterange

I want the input to be of the format dd/mm/yyyy and the output to be dd/mm/yyyy

At the moment the output is mm/dd/yyyy.  

How do I do this please?
0
robinbernett
Asked:
robinbernett
3 Solutions
 
ragnarok89Commented:
Sub x()
   newdate = Format(Range("T1").Value, "dd/mm/yyyy")
End Sub
0
 
lee555J5Commented:
Use the Format function.

Range("T1").Value = Format(daterange, "dd/mm/yyyy")

Lee
0
 
byundtCommented:
VBA is always expecting US format dates: mm/dd/yyyy. It can produce dd/mm/yyyy as a string using Format function--or the recipient cell can format the date using its built-in formatting (better alternative). The following macro is tolerant of leading zeros (or not) as well as use of two digits for the year.

Sub Dater()
Dim sDate As String
Dim dat As Double
Dim i As Long, j As Long, k As Long
For i = 1 To 3
    sDate = InputBox("Input date as dd/mm/yyyy", "Input DateRange")
    On Error Resume Next
    j = InStr(1, sDate, "/")
    k = InStr(j + 1, sDate, "/")
    dat = DateSerial(Mid(sDate, k + 1), Mid(sDate, j + 1, k - j - 1), Left(sDate, j - 1))
    On Error GoTo 0
    If dat <> 0 Then
        With Range("T1")
            .NumberFormat = "dd/mm/yyyy"
            .Value = dat
        End With
        Exit For
    Else
        MsgBox "There was an error in the date entry. Please try again."
    End If
Next
End Sub

Open in new window

0
 
robinbernettAuthor Commented:
Thank you all.

I much appreciate the additional understanding and learning, byundt.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now