Hi,
We've got a spreadsheet in Excel that we use as a time sheet. It gets imported into our Access database monthly so it's really important the data is in the correct format.
We've got what's known as a "Client Code" which consists of a "Client Number" (a number assigned to the client) and an "Episode Number" (a number assigned to how many times they've referred into the service). The format that needs to be kept on the time sheet is ####:# e.g. 1234:1 is the 1234th client and the first time they've used the service.
I've currently got the formula "=AND(LEN(E5)=6,ISNUMBER(--MID(E5,1,1)),ISNUMBER(--MID(E5,2,1)),ISNUMBER(--MID(E5,3,1)),ISNUMBER(--MID(E5,4,1)),MID(E5,5,1)=":",ISNUMBER(--MID(E5,6,1)))" which seems like a bit of a complicated way of achieving what I want - but it has done the job.
The one unfortunate issue with this formula is that a 3-digit client number requires a preceding '0' so client 123 must be entered as '0123'. This hasn't been a huge issue as we rarely see clients from that long ago still, HOWEVER - we're about to hit 10000 clients which means that my current formula won't work and if I just extend it to effective accept #####:# it will mean that all clients with four digits will require the preceding '0'. I'm sure that'll get annoying quite quickly and it's not particularly elegant.
TL:DR - I'd like to find a solution that allows me to force our staff to input the client code in the format #####:# without forcing a certain number of characters before (or, preferably, after) the colon.
Thanks in advance.
Bookmarks