populating cells based on references from other cells

[jennfurr]

I'll try to explain. I have two columns of military time (FROMTIME and TOTIME)

I have other columns within the same sheet titled 0000-0059, 0100-0159, 0200-0259, etc.

What I need to do is based on the FROMTIME and TOTIME is to place an X in the appropriate columns.

For example, if the FROMTIME is 1045 and the TOTIME is 1200, I need an X placed in the 1000-1059 column and the 1100-1159 column (not the 1200-1259 column, because the incident didn't potentially happen during that "hour span" - that's a management thing, long story).

Also, if an incident occurrs from 2300-0145, I need to be able to put the x in the last column (2300-2359) and the first column (0000-0059).

Is this do-able or is it another "can you total this diagonally for me?" type question?

 

[Mark W.]

Yes, that's because you didn't include that specification in your original requirements. Should I incorporate that one too?
This message was edited by Mark W. on 2002-04-17 16:04

 

[Mark W.]

Okay, that was easy enough. Use...

=IF(AND($A2>=LEFT(C$1,4),$A2<=RIGHT(C$1,4)),"X",
IF(AND($B2>LEFT(C$1,4),$A2<=RIGHT(C$1,4)),"X",
IF(AND($A2>$B2,$A2<=RIGHT(C$1,4)),"X",
IF(AND($A2>$B2,$B2>LEFT(C$1,4)),"X",""))))

...as before. This is just like writing a CASE statement... just think about the conditions you want to display an "X" and incorporate it into the nested IF.
This message was edited by Mark W. on 2002-04-17 15:27

 

[jennfurr]

Perfect! I'll make sure I give you the credit when my supervisor and coworkers see it.

You get the gold star for the day!!

 

[Jay Petrulis]

On 2002-04-17 16:15, jennfurr wrote:
Perfect! I'll make sure I give you the credit when my supervisor and coworkers see it.

You get the gold star for the day!!

No way! That's not how it works. YOU get to take all the credit. Forget Mark. You've never even heard of the Internet, let alone MrExcel.

Mention to everyone how it took a long time and a lot of effort, but you finally solved it.

Mark, nice job.

Bye,
Jay