Automatic date formating
 

I have the function below that sets dates up automatically, I mean, if I
type "2711" on column L, this number will be changed to 27/11/07.
This funcion works well with dates up to 31/12/07. I was trying to file a
date 31/01/08 and it does not work. For your reference I'm typing "310108".
What do I need to change to have that working properly?
Function is:

Option Explicit
Dim varDate As Variant
Dim sDate As String
Dim isect As Range


Private Sub Worksheet_Change(ByVal Target As Range)
'If cell changed is not in date sheet range then exit
Set isect = Application.Intersect(Range("L:L"), Target)
If isect Is Nothing Then Exit Sub

If Target.Value = "" Then Exit Sub

Application.EnableEvents = False
sDate = Format(Target.Value, "0000")
varDate = Left(sDate, 2) & "," & Mid(sDate, 3, 2)
On Error GoTo DateError
Target.Value = DateValue(varDate)
Application.EnableEvents = True
Exit Sub

DateError:
'If invalid date or cell not formated for dates then error occurs
MsgBox "Date Input Error"
Target.Value = ""
Application.EnableEvents = True
End Sub

Thanks a lot,

Gustavo Strabeli.

Automatic date formating
 

from:
varDate = Left(sDate, 2) & "," & Mid(sDate, 3, 2)
to
varDate =Mid(sDate, 3, 2) & "," & Left(sDate, 2) & "," & right(sDate, 2)

Notice I switch the MID and LEFT functions. Not sure if this code will work
internationally because of the way counties enter dates (months first verses
day first).


"Gustavo Strabeli" wrote:

I have the function below that sets dates up automatically, I mean, if I
type "2711" on column L, this number will be changed to 27/11/07.
This funcion works well with dates up to 31/12/07. I was trying to file a
date 31/01/08 and it does not work. For your reference I'm typing "310108".
What do I need to change to have that working properly?
Function is:

Option Explicit
Dim varDate As Variant
Dim sDate As String
Dim isect As Range


Private Sub Worksheet_Change(ByVal Target As Range)
'If cell changed is not in date sheet range then exit
Set isect = Application.Intersect(Range("L:L"), Target)
If isect Is Nothing Then Exit Sub

If Target.Value = "" Then Exit Sub

Application.EnableEvents = False
sDate = Format(Target.Value, "0000")
varDate = Left(sDate, 2) & "," & Mid(sDate, 3, 2)
On Error GoTo DateError
Target.Value = DateValue(varDate)
Application.EnableEvents = True
Exit Sub

DateError:
'If invalid date or cell not formated for dates then error occurs
MsgBox "Date Input Error"
Target.Value = ""
Application.EnableEvents = True
End Sub

Thanks a lot,

Gustavo Strabeli.

Automatic date formating
 

It worked!
Thanks, Joel.

"Joel" escreveu na mensagem
...
from:
varDate = Left(sDate, 2) & "," & Mid(sDate, 3, 2)
to
varDate =Mid(sDate, 3, 2) & "," & Left(sDate, 2) & "," & right(sDate, 2)

Notice I switch the MID and LEFT functions. Not sure if this code will work
internationally because of the way counties enter dates (months first verses
day first).


"Gustavo Strabeli" wrote:

I have the function below that sets dates up automatically, I mean, if I
type "2711" on column L, this number will be changed to 27/11/07.
This funcion works well with dates up to 31/12/07. I was trying to file a
date 31/01/08 and it does not work. For your reference I'm typing
"310108".
What do I need to change to have that working properly?
Function is:

Option Explicit
Dim varDate As Variant
Dim sDate As String
Dim isect As Range


Private Sub Worksheet_Change(ByVal Target As Range)
'If cell changed is not in date sheet range then exit
Set isect = Application.Intersect(Range("L:L"), Target)
If isect Is Nothing Then Exit Sub

If Target.Value = "" Then Exit Sub

Application.EnableEvents = False
sDate = Format(Target.Value, "0000")
varDate = Left(sDate, 2) & "," & Mid(sDate, 3, 2)
On Error GoTo DateError
Target.Value = DateValue(varDate)
Application.EnableEvents = True
Exit Sub

DateError:
'If invalid date or cell not formated for dates then error occurs
MsgBox "Date Input Error"
Target.Value = ""
Application.EnableEvents = True
End Sub

Thanks a lot,

Gustavo Strabeli.