Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Formatting a date from an input box
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Formatting a date from an input box
I am using an input box to enter a date. I'd like the end user to only have
to enter a 2-digit year, but have the system convert it to a 4 digit year.
How would I do that?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Formatting a date from an input box
Not sure where you are putting the output at, but you can use something like
this to do what you want...

Answer = InputBox("Enter a date")
If IsDate(Answer) Then Answer = Format(Answer, "m/d/yyyy")

to convert the user's input, if a valid date, to a 4-digit year. You can
then assign Answer to wherever you want.

--
Rick (MVP - Excel)


"posheroff" wrote in message
...
I am using an input box to enter a date. I'd like the end user to only
have
to enter a 2-digit year, but have the system convert it to a 4 digit year.
How would I do that?

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default Formatting a date from an input box
Is the user entering only a year and no other data information? Or is
the user entering a date with separators (e.g., mm/dd/yy) or a date
without separators (e.g., mmddyy)?

The following code will allow the user to enter a date in any of the
following formats and will convert it to a real date:

yy
m/d (current year assumed)
m/dd (current year assumed)
mm/d (current year assumed)
mm/dd (current year assumed)
mm/dd/ (current year assumed)
mm/dd/yy
mm/dd/yyyy
mmdd (current year assumed)
mmddyy
mmddyyyy

all other formats are invalid.


'''''''''''''''''''''''''''''''''''''''''''''''''
Sub AAA()
Dim S As String
Dim T As String
Dim DT As Date
Dim Sep As String
Dim N As Long
Sep = Application.International(xlDateSeparator)
S = Application.InputBox("Enter a date")
If StrPtr(S) = 0 Then
' user cancelled
Exit Sub
End If
N = InStr(1, S, Sep, vbBinaryCompare) 0
If N 0 Then
Select Case Len(S)
Case 3
' m/d
T = S & Sep & Format(Year(Now), "0000")
Case 4
If N = 2 Then
' m/dd
T = "0" & Left(S, 1) & Sep & Right(S, 2) & _
Sep & Format(Year(Now), "0000")
ElseIf N = 3 Then
' mm/d
T = Left(S, 2) & Sep & "0" & Right(S, 1) & _
Sep & Format(Year(Now), "0000")
Else
' invalid
T = S
End If
Case 5
' mm/dd
T = S & Sep & Format(Year(Now), "0000")
Case 6
' mm/dd/
T = S & Format(Year(Now), "0000")
Case 8
' mm/dd/yy
T = Left(S, 6) & "20" & Right(S, 2)
Case 10
' mm/dd/yyyy
T = S
Case Else

End Select
Else
Select Case Len(S)
Case 2
' yy
T = "1" & Sep & "1" & Sep & "20" & S
Case 4
' mmdd
T = Left(S, 2) & Sep & Right(S, 2) & Sep & _
Format(Year(Now), "0000")
Case 6
' mmddyy
T = Left(S, 2) & Sep & Mid(S, 3, 2) & Sep & _
"20" & Right(S, 2)
Case 8
' mmddyyyy
T = Left(S, 2) & Sep & Mid(S, 3, 2) & _
Sep & Right(S, 4)
Case Else
T = S
End Select
End If
On Error Resume Next
Err.Clear
DT = DateValue(T)
If Err.Number = 0 Then
MsgBox "Date Entered: " & DT
Else
MsgBox "Invalid Date: " & T
End If
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''



On Wed, 18 Mar 2009 13:06:41 -0700, posheroff
wrote:

I am using an input box to enter a date. I'd like the end user to only have
to enter a 2-digit year, but have the system convert it to a 4 digit year.
How would I do that?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Formatting a date from an input box
I believe the shorter macro will do what your code does (plus it allows for
all entries without a year to end in a slash, not just your "mm/dd/" one)...

Sub ConvertDate()
Dim D As Date
Dim S As String
Dim Sin As String
Dim Sep As String
Sep = Application.International(xlDateSeparator)
Sin = Application.InputBox("Enter a date")
S = Trim(Sin)
Do While Right(S, 1) = Sep
S = Left(S, Len(S) - 1)
Loop
On Error GoTo Whoops
If Len(S) = 2 Then
D = DateSerial(S, 1, 1)
ElseIf InStr(S, Sep) Then
D = CDate(S)
Else
S = Format(S, "!&&" & Sep & "&&" & "/" & "&&&&")
If Right(S, 1) = Sep Then S = Left(S, Len(S) - 1)
D = CDate(S)
End If
MsgBox "Date entered: " & D
Exit Sub
Whoops:
MsgBox "Invalid date: " & Sin
End Sub

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
Is the user entering only a year and no other data information? Or is
the user entering a date with separators (e.g., mm/dd/yy) or a date
without separators (e.g., mmddyy)?

The following code will allow the user to enter a date in any of the
following formats and will convert it to a real date:

yy
m/d (current year assumed)
m/dd (current year assumed)
mm/d (current year assumed)
mm/dd (current year assumed)
mm/dd/ (current year assumed)
mm/dd/yy
mm/dd/yyyy
mmdd (current year assumed)
mmddyy
mmddyyyy

all other formats are invalid.


'''''''''''''''''''''''''''''''''''''''''''''''''
Sub AAA()
Dim S As String
Dim T As String
Dim DT As Date
Dim Sep As String
Dim N As Long
Sep = Application.International(xlDateSeparator)
S = Application.InputBox("Enter a date")
If StrPtr(S) = 0 Then
' user cancelled
Exit Sub
End If
N = InStr(1, S, Sep, vbBinaryCompare) 0
If N 0 Then
Select Case Len(S)
Case 3
' m/d
T = S & Sep & Format(Year(Now), "0000")
Case 4
If N = 2 Then
' m/dd
T = "0" & Left(S, 1) & Sep & Right(S, 2) & _
Sep & Format(Year(Now), "0000")
ElseIf N = 3 Then
' mm/d
T = Left(S, 2) & Sep & "0" & Right(S, 1) & _
Sep & Format(Year(Now), "0000")
Else
' invalid
T = S
End If
Case 5
' mm/dd
T = S & Sep & Format(Year(Now), "0000")
Case 6
' mm/dd/
T = S & Format(Year(Now), "0000")
Case 8
' mm/dd/yy
T = Left(S, 6) & "20" & Right(S, 2)
Case 10
' mm/dd/yyyy
T = S
Case Else

End Select
Else
Select Case Len(S)
Case 2
' yy
T = "1" & Sep & "1" & Sep & "20" & S
Case 4
' mmdd
T = Left(S, 2) & Sep & Right(S, 2) & Sep & _
Format(Year(Now), "0000")
Case 6
' mmddyy
T = Left(S, 2) & Sep & Mid(S, 3, 2) & Sep & _
"20" & Right(S, 2)
Case 8
' mmddyyyy
T = Left(S, 2) & Sep & Mid(S, 3, 2) & _
Sep & Right(S, 4)
Case Else
T = S
End Select
End If
On Error Resume Next
Err.Clear
DT = DateValue(T)
If Err.Number = 0 Then
MsgBox "Date Entered: " & DT
Else
MsgBox "Invalid Date: " & T
End If
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''



On Wed, 18 Mar 2009 13:06:41 -0700, posheroff
wrote:

I am using an input box to enter a date. I'd like the end user to only
have
to enter a 2-digit year, but have the system convert it to a 4 digit year.
How would I do that?

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Formatting a date from an input box
In thinking about it, I *over protected* the code against slashes.
Correcting this reduces the code by two more lines...

Sub ConvertDate()
Dim D As Date
Dim S As String
Dim Sin As String
Dim Sep As String
Sep = Application.International(xlDateSeparator)
Sin = Application.InputBox("Enter a date")
S = Trim(Sin)
If Right(S, 1) = Sep Then S = Left(S, Len(S) - 1)
On Error GoTo Whoops
If Len(S) = 2 Then
D = DateSerial(S, 1, 1)
ElseIf InStr(S, Sep) Then
D = CDate(S)
Else
S = Format(S, "!&&" & Sep & "&&" & "/" & "&&&&")
If Right(S, 1) = Sep Then S = Left(S, Len(S) - 1)
D = CDate(S)
End If
MsgBox "Date entered: " & D
Exit Sub
Whoops:
MsgBox "Invalid date: " & Sin
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I believe the shorter macro will do what your code does (plus it allows for
all entries without a year to end in a slash, not just your "mm/dd/"
one)...

Sub ConvertDate()
Dim D As Date
Dim S As String
Dim Sin As String
Dim Sep As String
Sep = Application.International(xlDateSeparator)
Sin = Application.InputBox("Enter a date")
S = Trim(Sin)
Do While Right(S, 1) = Sep
S = Left(S, Len(S) - 1)
Loop
On Error GoTo Whoops
If Len(S) = 2 Then
D = DateSerial(S, 1, 1)
ElseIf InStr(S, Sep) Then
D = CDate(S)
Else
S = Format(S, "!&&" & Sep & "&&" & "/" & "&&&&")
If Right(S, 1) = Sep Then S = Left(S, Len(S) - 1)
D = CDate(S)
End If
MsgBox "Date entered: " & D
Exit Sub
Whoops:
MsgBox "Invalid date: " & Sin
End Sub

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
Is the user entering only a year and no other data information? Or is
the user entering a date with separators (e.g., mm/dd/yy) or a date
without separators (e.g., mmddyy)?

The following code will allow the user to enter a date in any of the
following formats and will convert it to a real date:

yy
m/d (current year assumed)
m/dd (current year assumed)
mm/d (current year assumed)
mm/dd (current year assumed)
mm/dd/ (current year assumed)
mm/dd/yy
mm/dd/yyyy
mmdd (current year assumed)
mmddyy
mmddyyyy

all other formats are invalid.


'''''''''''''''''''''''''''''''''''''''''''''''''
Sub AAA()
Dim S As String
Dim T As String
Dim DT As Date
Dim Sep As String
Dim N As Long
Sep = Application.International(xlDateSeparator)
S = Application.InputBox("Enter a date")
If StrPtr(S) = 0 Then
' user cancelled
Exit Sub
End If
N = InStr(1, S, Sep, vbBinaryCompare) 0
If N 0 Then
Select Case Len(S)
Case 3
' m/d
T = S & Sep & Format(Year(Now), "0000")
Case 4
If N = 2 Then
' m/dd
T = "0" & Left(S, 1) & Sep & Right(S, 2) & _
Sep & Format(Year(Now), "0000")
ElseIf N = 3 Then
' mm/d
T = Left(S, 2) & Sep & "0" & Right(S, 1) & _
Sep & Format(Year(Now), "0000")
Else
' invalid
T = S
End If
Case 5
' mm/dd
T = S & Sep & Format(Year(Now), "0000")
Case 6
' mm/dd/
T = S & Format(Year(Now), "0000")
Case 8
' mm/dd/yy
T = Left(S, 6) & "20" & Right(S, 2)
Case 10
' mm/dd/yyyy
T = S
Case Else

End Select
Else
Select Case Len(S)
Case 2
' yy
T = "1" & Sep & "1" & Sep & "20" & S
Case 4
' mmdd
T = Left(S, 2) & Sep & Right(S, 2) & Sep & _
Format(Year(Now), "0000")
Case 6
' mmddyy
T = Left(S, 2) & Sep & Mid(S, 3, 2) & Sep & _
"20" & Right(S, 2)
Case 8
' mmddyyyy
T = Left(S, 2) & Sep & Mid(S, 3, 2) & _
Sep & Right(S, 4)
Case Else
T = S
End Select
End If
On Error Resume Next
Err.Clear
DT = DateValue(T)
If Err.Number = 0 Then
MsgBox "Date Entered: " & DT
Else
MsgBox "Invalid Date: " & T
End If
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''



On Wed, 18 Mar 2009 13:06:41 -0700, posheroff
wrote:

I am using an input box to enter a date. I'd like the end user to only
have
to enter a 2-digit year, but have the system convert it to a 4 digit
year.
How would I do that?


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
input a date or update it based on date in another cell Doug P New Users to Excel 1 July 18th 07 11:25 PM
Input Box Formatting Doctorjones_md Excel Programming 2 January 31st 07 11:38 PM
formatting input boxes cereldine[_38_] Excel Programming 3 June 16th 06 02:37 PM
Conditional formatting - different cell colour for each year following from user input date? StargateFan[_3_] Excel Programming 20 January 11th 06 07:11 AM
Input box to formatting???? Simon Lloyd[_412_] Excel Programming 1 May 6th 04 01:27 PM


All times are GMT +1. The time now is 01:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement