How to validate a time value 1

[wec43wec]

I have users entering data in column A1 thru A1000.

I use these "time" entries HH:MM elsewhere in the spreadsheet that looks for a time value. The problem is that some users will enter the time "incorrectly". I want to discard and/or show a blank if the entry is NOT a time value (HH:MM)

example - the user may enter "900" when they should have entered the time as 9:00.

Thanks for any help

 

[macropod]

Hi wec43wec,

You could use a Worksheet_Change sub inserted into the relevant worksheet module like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Range("A1:A1000")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  With Target
    If .Value = "" Then Exit Sub
    If .Value < 1 And Format(.Value, "00:00:00") Like "##:##:##" Then Exit Sub
  End With
  MsgBox "Not a valid time.", vbExclamation + vbOKOnly
  .Undo
  .EnableEvents = True
End With
End Sub

Cheers

[MS MVP - Word]

 

[wec43wec]

Macropod - thanks for the reply, however I need to find a way to complete this task without using VBA.

Is this possible?

 

[voisey]

wec43wec - have you tried using data validation?

Data / Allow : time
between 00:00:00 and 23:59:59 - you can then enter an input message advising the user what they are restricted in entering.

Highlight A1 through to A1000 and try it


Voisey

 

[voisey]

Sorry - forgot to mention - column A should be formatted as 'time' as well

Voisey

 

[wec43wec]

Yes - I am aware of data validation but I do not control the program> I have made a request/suggestion upstream in my organization but as of this date, they have not decided to set this criteria in excel.

So I am back to my original question of trying to test a cell (s) to determine if it contains a time value before I continue with a formula (not using VBA).

If you have any other ideas, please advise.

Thanks

 

[SkipVought]

Check out the CELL function, that returns D6 thru D9.

Skip,
sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wec43wec]

Skip - can you provide a sample formula?

Check to see if cell A1 contains a number or a time value.

I looked at the help screen for cell function but was not clear of how to use the differnet codes that are listed.

Thanks

 

[SkipVought]

[tt]
9:00 D9 =CELL("format",A1)
900 G
[/tt]

[tt]
If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wec43wec]

Skip - excellent !

Thanks

 

[macropod]

Hi wec43wec,

It would have been nice had you stated up front that you couldn't use either vba or data validation. It would have saved both me and voisey wasting our time.

Cheers

[MS MVP - Word]

 

[wec43wec]

Macropod - you should never consider your response as a waste of time. Even, if I do not use VBA, your suggestion could be helpful to others. I try to learn from every post made in this forum and did not think to indicate or request a “non VBA” solution. I kind of felt this way because of comments I've previously seen in this forum that suggest users to use the VBA forum and not the MS Office forum for VBA solutions.

The other issue in term of acknowledging my attempts to use Data Validation. I know about this function, but could not use and did not use it because I do not control the program. There were a number of other attempts (other functions) I made which I think would have cloud the original message had I listed those attempts in my original post.

I certainly respect your request/suggestion and will do my best to comply as much as possible in the future.

I think very highly of many responses from experts such as yourself (Skip, XLBO, GlennUK, etc), and would not want to do anything to tarnish those efforts.

Respectfully

WEC43WEC