Conditional format

**DS00** · 03-15-2014, 12:35 PM

Hey guys I have this issue,

I have a columm that is suposed to have only time values (format= hh:mm) and it must be filled with that format.

Nevertheless, sometimes when my assistants fill the columm they insert the values as (for eg.) 10.45 instead of 10:45 which changes the cell format and will give me a wrong result in another cell.

What I was wondering is if it is possible to make a conditional format which warns me (with a color change for instance) when the cell format is changed.
I have a very large list and It would spare me a great amount of time checking if there is any value with "." instead of ":"

I hope I've made myself clear.

Cheers.

DS

**bebo021999** · 03-15-2014, 12:45 PM

Assuming the time you enter is <=24:00

Conditional formating/Use a formula...:

=A1>1

will marks cell A1 if the value is not the time format

**martindwilson** · 03-15-2014, 02:35 PM

try this conditional format
=NOT(OR(A1="",AND(CELL("format",A1)>="D6",CELL("format",A1)<="D9")))
so if someone enters 00:45 or 10:45 its fine if they enter 0.45 or 10.45 the cell will change colour

**DS00** · 03-15-2014, 05:27 PM

Originally Posted by martindwilson

try this conditional format
=NOT(OR(A1="",AND(CELL("format",A1)>="D6",CELL("format",A1)<="D9")))
so if someone enters 00:45 or 10:45 its fine if they enter 0.45 or 10.45 the cell will change colour

Thanks for the help. Sorry for not getting it right but when you mean D6 and D9 is it suppose to mean true and false on the conditional formating? And A1 I assume would be the cell I want to apply the formatting.

Thanks once again.

**FDibbins** · 03-15-2014, 05:58 PM

"D6" and "D9" are cell formats, not references to cell D6 or D9. "D9" represents time format.

Another approach to this would be to prevent the error in the 1sy place (wont fix the current errors though)

You could do this by applying Data Validation to those cells, the the rule shown below, this will allow only time format to be entered.

Highlight the range, select Data Validation from the data Tab
select Custom from the Allow drop-down
Formula =CELL("format",A1)="D9"
test to make sure it's working

**DS00** · 03-15-2014, 06:14 PM

Man, problem solved!

Thanks a lot! Didnt know about this website but surely I'll come and visit again.

Cheers!

**FDibbins** · 03-15-2014, 06:27 PM

happy to help and look forward to seeing you around here again soon

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**DS00** · 03-15-2014, 06:31 PM

Done!

Cya later

Conditional format

LinkBack

Thread Tools

Rate This Thread

Display

Conditional format

Re: Conditional format

Re: Conditional format

Re: Conditional format

Re: Conditional format

Re: Conditional format

Re: Conditional format

Re: Conditional format

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need conditional format to check prior sheet for a given number and change format if found

[SOLVED] Copy Conditional Format only without over writing existing format

[SOLVED] Is it possible to format cells based on their value? (Not a conditional format query)

Excel 2007 : Conditional Format based on another worksheet conditional format

[SOLVED] format cells yyyymmdd and then conditional format

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions