Color conditionning time format

[djsouljah]

Hi There!

Need your help plz..

I have 3 columns that have data in time HH:MM:SS format

I would like to use color conditioning on them..but it just won't work no matter what I try...

For example if the amount is between 00:00:40 and 00:00:50 the color should turn red.

PS: how to we attach files to this forum so u can take a look at the file? or does it have to be external link ?

Thanks for your help.

 

[asking]

Your first post you asked

"PS: how to we attach files to this forum so u can take a look at the file? or does it have to be external link ?"

A file is too big to post but you can post part of a file as a table

 

[djsouljah]

Thank you guys! The issue is resolved! Great work!

I have something to ask though..as u may have noticed, some of the reseults are giving #NA..

How would I Make it display "absent" instead of "#NA" error?

an example with this formula would help:

=INDEX('YTD Stats'!$J$3:$J$20000,MATCH(Table1[Aspect ID]&'Week 2 Week Stats'!$A8,INDEX('YTD Stats'!$C$3:$C$20000&'YTD Stats'!$D$3:$D$20000,,),0))

 

[djsouljah]

I tried this:

=IF(ISERROR(INDEX('YTD Stats'!$J$3:$J$20000,MATCH(Table1[Aspect ID]&'Week 2 Week Stats'!$A21,INDEX('YTD Stats'!$C$3:$C$20000&'YTD Stats'!$D$3:$D$20000,,),0)),""))

But it tells me that I have entered too many arguments for this function..

What am i doing wrong?

 

[asking]

Try ~

=IF(ISNA(INDEX('YTD Stats'!$J$3:$J$20000,MATCH(Table1[Aspect ID]&'Week 2 Week Stats'!$A8,INDEX('YTD Stats'!$C$3:$C$20000&'YTD Stats'!$D$3:$D$20000,,),0))),"absent",INDEX('YTD Stats'!$J$3:$J$20000,MATCH(Table1[Aspect ID]&'Week 2 Week Stats'!$A8,INDEX('YTD Stats'!$C$3:$C$20000&'YTD Stats'!$D$3:$D$20000,,),0)))

 

[djsouljah]

Says the formula I entered contains an error

 

[asking]

When I gave the formula to give absent instead of #N/A I did not know that the formula had too many arguments.

Try to find in your formula where you are going wrong by using evaluate formula in the formulas menu.

I can't test as I don't have the sheets ~ info.

 

[djsouljah]

I did try but can't figure it out..

I put the file in a previous post: here you go

http://www.mediafire.com/?g02j64l1hje4s41

 

[asking]

I have played around and can find no fault in this formula

Save your sheet first then copy and paste this formula into cell C8

=IF(ISNA(INDEX('YTD Stats'!$J$3:$J$20000,MATCH(Table1[Aspect ID]&'Week 2 Week Stats'!$A8,INDEX('YTD Stats'!$C$3:$C$20000&'YTD Stats'!$D$3:$D$20000,,),0))),"absent",INDEX('YTD Stats'!$J$3:$J$20000,MATCH(Table1[Aspect ID]&'Week 2 Week Stats'!$A8,INDEX('YTD Stats'!$C$3:$C$20000&'YTD Stats'!$D$3:$D$20000,,),0)))

In your table1 select DAOUDI, NAJAT ~ now Cell C8 when calculated should now show absent.

Cheers

 

[djsouljah]

Thanks man! Everythings resolved! Thank you your wonderful and fast help! ur great!

 

[asking]

thanks for the thanks

it feels good to help when appreciated.