Put format condition in IF statement?

P

Phil C

Hi All

With some help from people in this NG I have managed (using data validation)
to trap an error where the user does not use the correct time format
(hh:mm). Basic setup: time values (format hh:mm) in A1 and A2. A3 (formatted
as number) contains formula: =A1-A2. OK, but if user types too quickly and
enters 1400 in A1 instead of 14:00, A3 displays "rubbish". The program
(graph based in part on value in A3) doesn't fail because the user is forced
to retry, but the rubbish in A3 is still visible alongside the data
validation dialogue box, so looks untidy.

Is there a way to put something in A3 to the effect: =IF(format of A1 not
hh:mm, "wrong format", A1-A2)?

Thanks for your help

Phil
 
P

Phil C

Dave

This is a slightly different problem. As I said, the previous suggestion
solved the main problem. I am now just trying to sort out the 'loose end'.
That is, the untidy appearance of a cell whose value depends on the data
entry cell in question.

Phil
 
D

Dave Peterson

Ahhh.

But if data|validation stops the invalid entry, how could the invalid
data/format be entered?

(You could do copy|paste, but that destroys the Data|validation, too.)

Maybe you could just check for numeric data in your formula cell (and allow
Data|validation to catch all the other errors):

=if(isnumber(a1),a1-a2,"error in A1")

But I think I'm missing something.
 
P

Phil C

Hi Dave

Thats's the point. The validation criteria doesn't actually stop the data
entry, in the sense that dependent cells are still evaluated, resulting in
an error value or nonsensical numeric value (as in my case). OK, the user
is forced back to the problematic entry (by the validation STOP message),
but the dependent cell is still visible until the entry is corrected. Your
suggestion works if the user enters text by mistake (this is quite possible
as there are quite a few input cells, some of which requuire text!) but
ISNUMBER (nor any of the other IS functions as far as I can see) seem to be
able to differentiate 13:30 from 1330.

Thanks for your patience!

Phil
 
D

Dave Peterson

Maybe you could check to see if it's less than 1.

=IF(AND(ISNUMBER(A1),A1>1),"",A1+0.5)

But I think you're the first person I've seen say that the momentary display of
those calculations is a problem (since they revert as soon as the user dismisses
the dialog).

Maybe you could use a worksheet event to do the validation. I could see the
calculations take place (on a slow pc watching closely), but they revert pretty
darn quickly and aren't visible when the message box appears:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
If .Value > 1 Then
With Application
.EnableEvents = False
.Undo
MsgBox "Please enter a time!"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab and select view code. Paste this into the code
window and then back to excel to test it out. I used A1 for my test cell.






Phil said:
Hi Dave

Thats's the point. The validation criteria doesn't actually stop the data
entry, in the sense that dependent cells are still evaluated, resulting in
an error value or nonsensical numeric value (as in my case). OK, the user
is forced back to the problematic entry (by the validation STOP message),
but the dependent cell is still visible until the entry is corrected. Your
suggestion works if the user enters text by mistake (this is quite possible
as there are quite a few input cells, some of which requuire text!) but
ISNUMBER (nor any of the other IS functions as far as I can see) seem to be
able to differentiate 13:30 from 1330.

Thanks for your patience!

Phil
Click to expand...
 
Top