Time input error

[Tomwhite]

I have a spreadsheet where I record employees time clock punch in and punch out times. Occasionally I will have a typo where I enter 8;00 instead of 8:00. This creates a problem in another part of the workbook that is dependent on this data. I'm looking for a way to flag this so I know it is an error. Maybe with conditional formatting where the color changes. Any other method may work; I just need it to stand out.

Thanks,
Tom

 

[sprigelz]

Would something like this work for you?

 

[Tomwhite]

Thanks much for the reply. That's the idea, but why doesn't this work?

 

[Joe4]

Do you mean why doesn't the value of "5;15" in column D highlight?
It is because your formula locks it down to only look at column A.

Remove the column lock in ($) your formula:

=FIND(";",A1,1)

 

[Tomwhite]

That works well in my sample file. When I put it into the actual working file it doesn't. And I have one empty cell that turns red. M28

 

[Tomwhite]

That works well in my sample file. When I put it into the actual working file it doesn't. And I have one empty cell that turns red. M28

View attachment 71867

And I'd like to use a cell other than A1.

 

[Joe4]

And I'd like to use a cell other than A1.

Yes, that is just the example based on the original image posted.
You change it to match whatever range you are applying it to.
Whenever you write a Conditional Formatting formula to a multi-cell range, you write the formula as it applies to the very first cell in the range you are applying it to.

Since you are applying that condition to the range E7:BF30, the formula you type in to that rule should be:

=FIND(";",E7,1)

 

[Joe4]

Note: If it were me, I might take a different approach, such as using VBA to not allow them to enter anything that is not a valid time.
As long as you don't want to allow them to enter anything other than a valid time in the range, we can prevent any entries that are not valid times/dates.

 

[Tomwhite]

I think we'll stay with this. You have been awesome and your previous reply has solved my problem. Thank you for the help and thank you for doing so quickly!
Tom

 

[Joe4]

You are welcome!
Glad we were able to help!