Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Format With Leading Zeros

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •