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.]

What's the data type of FROMTIME and TOTIME?

[Mark W.]

Consider this where A1:F4 contains...

{"FROMTIME","TOTIME","0000-0059","0100-0159","0200-0259","0300-0359"
;"0025","0101","X","X","",""
;"0133","0301","","X","X","X"
;"0145","0300","","X","X",""}

...and cell C2 (the 1st "X" above) contains the formula...

=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",""))

...which is filled right to column F:F and filled down to row 4:4.

[ This Message was edited by: Mark W. on 2002-04-17 12:31 ]

[jennfurr]

I do it one of two ways -

Formatted as a custom type "0000", or as time in hh:mm format "14:35"

I get the information in a data dump from a mainframe, which gets read as a number field, and I usually have that transferred as =(LEFT,a2,2)&":"&(RIGHT,A2,2) into a time formatted field.

It just depends if I'm going to be fiddling with time calculations or not. So I'm flexible. If one way makes it easier than the other, then I'll do it that way.

[Mark W.]

I'm guessing that you missed my reply above. Take a look!

[jennfurr]

heh, I replied while you were replying, I think

Anyway, that WORKED! Thank you!!!!!

Usually, it's Aladin who gives me the only concise, working solution.

You rool. I figured out what half of that formula means, now I just have to figure out the rest of it all that embedding. I'll get it, eventually...

[jennfurr]

oops! if I put in FROMTIME 2345 and TOTIME 0125, it doesn't work

Seem like it can't handle wrapping from one "day" to the next.

[Mark W.]

On 2002-04-17 12:54, jennfurr wrote:
oops! if I put in FROMTIME 2345 and TOTIME 0125, it doesn't work

Seem like it can't handle wrapping from one "day" to the next.

I overlooked that "last" requirement. Let me see what I can do.

[ This Message was edited by: Mark W. on 2002-04-17 13:28 ]

[Mark W.]

Okay, try this in cell C2 (following the same instructions as above)...


=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,OR(LEFT(C$1,4)="0000",RIGHT(C$1,4)="2359")),"X",""))))

[jennfurr]

almost works...

when I put in something like 2200-0058, that works, but when i put in 2200-0700, it only captures the 2200-2259, 2300-2359, and 0000-0059 columns. It didn't get the other columns up to 0600-0659.