Format With Leading Zeros

[Fun_Geek]

The User will enter a 8 digit date into a cell WITHOUT "/" separators, i.e., 01152002, representing January 15, 2002.

I would like the date displayed the same (01152002). It should not require the user to lead entry with apost (').

Also, another requirement is to validate date code on entry. It must be a valid calendar date (not something like January 34, 2002).

TIA

Mike

[daleyman]

okay, for the format set a custom format as follows (right click the cell and choose Format Cells, then on the number tab select custom and enter the following formula):

ddmmyyyy

secondly, for the validation, select the cells and choose Validation from the Data menu, and set date and put in a lowest possible and highest possible date that the use could enter, eg. 01/01/90 and 01/01/20.

[Yogi Anand]

On 2002-04-13 15:06, Fun_Geek wrote:
The User will enter a 8 digit date into a cell WITHOUT "/" separators, i.e., 01152002, representing January 15, 2002.

I would like the date displayed the same (01152002). It should not require the user to lead entry with apost (').

Also, another requirement is to validate date code on entry. It must be a valid calendar date (not something like January 34, 2002).

TIA

Mike

Hi Fun_Greek and daleyman:
daleyman's method did not work for me -- this is what did work for me
if you have the text value 1152002, 01152002 in cell A1, then in cell A2 put

=TEXT(A1,"00-00-0000")+0

This will convert your 7-digit or 8-digit text entry to a valid date simulating the day, the month, and the year in the text string.
HTH!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:39 ]

[Fun_Geek]

Daleyman,

You said, "okay, for the format set a custom format as follows (right click the cell and choose Format Cells, then on the number tab select custom and enter the following formula): ddmmyyyy"

This doesn't work because:

(1) The user will enter in the format MMDDYYYY (even formatting as mmddyyyy) didn't work.

(2) Entering 01152002 in your above example will display "01265054" in the cell and "01/26/5054" in the formula bar.

You also said, "secondly, for the validation, select the cells and choose Validation from the Data menu, and set date and put in a lowest possible and highest possible date that the use could enter, eg. 01/01/90 and 01/01/20."

This will not work since the user will not be entering the "/". Again in the orignial post, I indicated that the user will enter directly to the cell "01152002" - no special characters for formatting.

Mike

[daleyman]

okay, try this then, pasting it into the module relating to the worksheet you are on.


Dim daypart As Integer, monthpart As Integer, yearpart As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Target.Value) Then Exit Sub
If Len(Target.Value) < 7 Then Exit Sub
strg = Target.Value
If Len(strg) = 7 Then strg = "0" & strg

'validation

valid = True
If Len(strg) <> 8 Then valid = False
If IsNumeric(strg) = False Then valid = False
If valid = True Then
daypart = Abs(Left(strg, 2))
monthpart = Abs(Mid(strg, 3, 2))
yearpart = Abs(Right(strg, 4))
If daypart < 1 Or daypart > 31 Then valid = False
If monthpart < 1 Or monthpart > 12 Then valid = False
If yearpart < 1900 Or yearpart > 2050 Then valid = False
End If

'result
If valid = True Then
Target.NumberFormat = "DDMMYYYY"
Target.Value = DateSerial(yearpart, monthpart, daypart)
Else
Target.Value = "ERROR"
End If
End Sub


...only problem is it does it for the whole sheet, if you want it to cover only one column, for example column B, insert after the first line of code...

If Target.Column <> 2 Then Exit Sub

...good luck this time!

[Tom Schreiner]

Hi
Assumes numbers are entered into column A
I may be the King of overkill, but it works..
Edit to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Len(Target) = 0 Then Exit Sub
Application.EnableEvents = False
If Len(Target) = 7 Then Target = "'0" & Target
If Not IsDate(Strings.Left(Target, 2) & "/" & _
Strings.Mid(Target, 3, 2) & "/" & Strings.Right(Target, 4)) Then
MsgBox "Invalid Date Entered"
Target.Select
Application.EnableEvents = True
Exit Sub
End If
Cells(Target.Row, Target.Column + 1).Value = _
Format(Strings.Left(Target, 2) & "/" & _
Strings.Mid(Target, 3, 2) & "/" & Strings.Right(Target, 4), _
"DDDD, MMMM DD, YYYY")
End If
Application.EnableEvents = True
End Sub

Tom

[ This Message was edited by: TsTom on 2002-04-13 20:20 ]

[daleyman]

well it's less lines of code than mine, does that make me the Emperor of Overkill?

[Tom Schreiner]

Sorry Daley,
I would not have even posted it if I would have seen your post...
Had to sign off right in the middle of writing it.
Tom

[daleyman]

not at all, is always interesting to see different ways of skinning the same cat.

[Fun_Geek]

How would I proceed with the code? Assume pasting it somewhere into the VBE...