populating cells based on references from other cells

jennfurr

New Member
Joined
Apr 8, 2002
Messages
38
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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",""))))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top