CODEWITHSUNDEEP
excelvalidationvbscript
user9519471
user9519471

Reputation:

cell format/data validation/vba to ensure the correct entry in a cell

We have an invoice # format the is very strict and must match a certain format. We do this manually and I often get multiple variations so was trying to create data validation to control the entry of the invoice number.

Here’s the format:

YYYYMMDD-RNN

  1. The invoice number is exactly 12 characters long
  2. The first 4 characters are the year (full year, like 2018)
  3. The next 2 characters are the month (like 02, must have the leading zero for 1 to 9) and not allow higher that 12.
  4. The next 2 characters are the day (like 08, must have the leading zero for 1 to 9) and not allow higher that 31.
  5. The next character is a “-“
  6. The next character is a region identifier. Allowable numbers are 0 to 9.
  7. The final 2 characters are sequential #’s beginning with 01. Must have the leading zero for 1 to 9.

Need a formula to validate this.

Upvotes: 0

Views: 199

Answers (2)

user4039065
user4039065

Reputation:

Try this UDF in a standard module code sheet.

Option Explicit

Function invoiceCheck(rng As Range) As Boolean
    Dim tmp As String

    tmp = rng.Value2

    'check length
    If Len(tmp) <> 12 Then Exit Function

    'check valid date
    If Not IsDate(Join(Array(Mid(tmp, 5, 2), Mid(tmp, 7, 2), Left(tmp, 4)), "/")) Then Exit Function

    'check date is today or earlier
    If CDate(Join(Array(Mid(tmp, 5, 2), Mid(tmp, 7, 2), Left(tmp, 4)), "/")) > Date Then Exit Function

    'make sure there is a hyphen
    tmp = Split(tmp, "-")(1)

    invoiceCheck = IsNumeric(tmp)
End Function

Upvotes: 0

bezurli
bezurli

Reputation: 65

The following formula should work, but I couldn't insert it on custom data validation. I suspect there's a character limit. If anyone could shed a light, it would be nice.

=IF(AND(LEFT(A1;4)*1<=YEAR(TODAY());LEFT(A1;4)*1>=2000);IF(AND(MID(A1;5;2)*1>=1;MID(A1;5;2)*1<=12);IF(AND(MID(A1;7;2)*1>=1;MID(A1;5;2)*1<=30);IF(MID(A1;9;1)="-";IF(AND(MID(A1;10;1)*1>=0;MID(A1;10;1)*1<=9);IF(AND(MID(A1;11;2)*1>=1;MID(A1;11;2)*1<=99);TRUE;FALSE);FALSE);FALSE);FALSE);FALSE);FALSE)

P.S.: I assumed no invoices after the year 2000.

Upvotes: 1

Related Questions