countif question

[stokie123]

Hi

I am using the COUNTIF formula in a spreadsheet but i am struggling with a
part of it.

Column C has start times and column D has finish times. In row 1 from column
P to KR is 24 hour clock at 5 minute intervals. Column O has names.

This is my formula at present which works.

=COUNTIF($C3:$D3,P$1:KR$1)

This only highlights the start time and finish time. I also want to
highlight the time inbetween the start and finish time.

I hope i explained this well enough for you to help.

 

[stokie123]

I have now found out that 2003 excel does not go from P to KR range in the
COUNTIF formula so I am now guessing that i need to use some other
formula........


Help!!

 

[JLatham]

I'm a bit confused as to what your goal is. You mention highlighting cells,
but which cells are you wanting to highlight?

Are you wanting to highlight the cells on the same row with a start time &
end time & name underneath the columns (P:KR) where the times in row 1
'overlap' the start and end times (columns C)?

If this is the case, then you could put this formula in P3:
=IF(AND(P$1>=$C3,P$1<=$D3),1,0)
and fill it out to the right to the end and then set conditional formatting
of those cells to become highlighted when their value = 1.

Hope this helps some.

 

[stokie123]

Hi

Thanks for your reply. The formula works great for what i need.


Thankyou for your help.

 

[stokie123]

Hi,

I have another question if you can help.

=IF(AND(P$1>=$C3,P$1<=$D3),1,0) works great but i need it to ignore blank
cells. It is returning a value of 1 for blank cells.

how do I get it to do that?

 

[JLatham]

I think what you need is this:
=IF(ISBLANK(P$1),0,IF(AND(P$1>=$C3,P$1<=$D3),1,0))

Since I only got a 1 when values in row 1 were blank/missing and not when
values in C or D were missing.