If statements involving time

[sg547]

Trying to ID a person who opens or closes the store. So,if h9=time person is
sheduled for, then O for Opener, C for Closer, else blank. B7=10:00 PM
=Closer and so on. =IF(H9=$B$7,"C",IF(H9=$B$5,"C",IF(H9=$B$3,"C",""))) Help.

 

[JE McGimpsey]

Trying to ID a person who opens or closes the store. So,if h9=time person is
sheduled for, then O for Opener, C for Closer, else blank. B7=10:00 PM
=Closer and so on. =IF(H9=$B$7,"C",IF(H9=$B$5,"C",IF(H9=$B$3,"C",""))) Help.

I'm sure this makes perfect sense to you, since you're probably looking
at the worksheet, but to me it's ambiguous at best.

For instance, if B7 = 10:00 pm, what are B5 and B3, and why would they
also be (apparently) times for Closers? What values are in those cells?
Are they stored as XL times?

If you give more detail on what you're trying to accomplish, and how
your data is laid out, it'll be easier to make a suggestion.

 

[PhilD]

Trying to ID a person who opens or closes the store. So,if h9=time person is
sheduled for, then O for Opener, C for Closer, else blank. B7=10:00 PM
=Closer and so on. =IF(H9=$B$7,"C",IF(H9=$B$5,"C",IF(H9=$B$3,"C",""))) Help.

As a general point, times are treated as fractions of a day (so 12
o'clock noon is 0.5, 6pm is 0.75, etc.). Does it help to state
IF(b3>0.5,"O",""), or whatever time/cell you need?

PhilD

 

[sg547]

As a general point, times are treated as fractions of a day (so 12
o'clock noon is 0.5, 6pm is 0.75, etc.). Does it help to state
IF(b3>0.5,"O",""), or whatever time/cell you need?

PhilD

 

[PhilD]

Is there someplace that identifies what 10am or pm for example would look like if I were to use this format?

If all else fails, you can calculate from first principles.

Use the 24-hour clock.

10am = 10/24 days
10pm = 22/24 days

If you need minutes, then:
07:30 = (7 hours + 30mins/60mins)/24 = 7.5/24 days
3:24pm = (15 + 24/60)/24 days

If you really, really need seconds, too, then:
1:23:45pm = (13 + 23/60 + 45/3600)/24 days (there are 3600 seconds in
1 hour).

PhilD

 

[sg547]

I'm sure this makes perfect sense to you, since you're probably looking
at the worksheet, but to me it's ambiguous at best.

For instance, if B7 = 10:00 pm, what are B5 and B3, and why would they
also be (apparently) times for Closers? What values are in those cells?
Are they stored as XL times?

If you give more detail on what you're trying to accomplish, and how
your data is laid out, it'll be easier to make a suggestion.

I have a group of people who are scheduled to start work throughout the day,
but those who are scheduled between 6am thru 10am are my Openers and those
scheduled between 10pm thru 12am are my Closers. I need to identify them
quickly so I inserted 2 columns and developed If statements to flag me with
an “O†or a “C†beside the time columns. Then, at the bottom of each column
I added a Countif to show me how many Closers and Openers I have scheduled
for any given day. I need 30 minute increments from 6:00 AM to 10:00 AM for
Openers and from 10:00 PM to 12:00 AM for Closers, so even if I can get the
If statements to work, I’m afraid there will be too many nests to work
properly. So, B5=10:00 PM, B7=11:00 PM and so on. Each should give me a “Câ€
beside the time column. What’s been happening is just the opposite. It
returns a C or O if the time is blank. Hope this helps, thanks so much for
any help you can give.

 

[JE McGimpsey]

I have a group of people who are scheduled to start work throughout the day,
but those who are scheduled between 6am thru 10am are my Openers and those
scheduled between 10pm thru 12am are my Closers. I need to identify them
quickly so I inserted 2 columns and developed If statements to flag me with
an “O†or a “C†beside the time columns. Then, at the bottom of each column
I added a Countif to show me how many Closers and Openers I have scheduled
for any given day. I need 30 minute increments from 6:00 AM to 10:00 AM for
Openers and from 10:00 PM to 12:00 AM for Closers, so even if I can get the
If statements to work, I’m afraid there will be too many nests to work
properly. So, B5=10:00 PM, B7=11:00 PM and so on. Each should give me a “Câ€
beside the time column. What’s been happening is just the opposite. It
returns a C or O if the time is blank. Hope this helps, thanks so much for
any help you can give.

I'll freely admit that I'm still confused...

I might approach it this way:

=IF(H9="","", IF(OR(H9<TIME(1,0,0), H9>=TIME(22,0,0)), "C",
IF(H9<=TIME(10,0,0), "O", "")))

 

[Carl Witthoft]

I'll freely admit that I'm still confused...

I might approach it this way:

=IF(H9="","", IF(OR(H9<TIME(1,0,0), H9>=TIME(22,0,0)), "C",
IF(H9<=TIME(10,0,0), "O", "")))

It strikes me that, so long as the OP strictly limits his times to exact
half hours, a CASE construction would be the simplest. Ooops, Excel
calls it something else... CHOOSE. So set up a cell somewhere which
divides his times down to integers from 1 to (whatever), and use CHOOSE
to get the value he wants.

 

[sg547]

Hope this is legible enough for you to see what I am trying to accomplish.
Thanks again for any help you can offer.
K L M N P Q R S
Start End Shift Starts Shift Ends
O 6:00 AM 11:00 PM 6:00 AM 0.2500 10:00 PM 0.9167
C 2:30 PM 10:30 PM 6:30 AM 0.2708 10:30 PM 0.9375
O 8:30 AM 5:00 PM 7:00 AM 0.2917 11:00 PM 0.9583
O 9:00 AM 5:00 PM 7:30 AM 0.3125 11:30 PM 0.9792
2:30 PM 11:00 PM 8:00 AM 0.3333 12:00 AM 0
8:00 AM 11:00 PM 8:30 AM 0.3542
3 1 9:00 AM 0.3750

Column M=Start of Shift, Column N=End of Shift. The formula below is
supposed to identify those people who are scheduled to close the
store. Column K signifies an O for a person scheduled to open the store
and Column L signifies a C for a person scheduled to close the store.

IF(M17="","",IF(M17=$S$9,"C",IF(M17=$S$10,"C",IF(M17=$S$11,"C",IF(M17=$S$12,"C",IF(M17=$S$13,"C",""))))))

 

[JE McGimpsey]

It strikes me that, so long as the OP strictly limits his times to exact
half hours, a CASE construction would be the simplest. Ooops, Excel
calls it something else... CHOOSE. So set up a cell somewhere which
divides his times down to integers from 1 to (whatever), and use CHOOSE
to get the value he wants.

Given that there are only two breakpoints (three with the rollover at
midnight), CHOOSE seems like overkill to me, especially if you put an
artificial limit on the user. Some preprocessing would be required in
any case, since there are 48 half hours per day, and there's a limit of
30 arguments in a the CHOOSE function.

 

[sg547]

I have never heard of CHOOSE, but I will look into it. It may not be
overkill since I am looking at specific times, not the entire 24 hour
spectrum. Those times are from 6am to 9am in half hour increments and from
10pm to 12am again in half hour increments. I will go into my programs help
index and see how CHOOSE is constructed. Thanks so much for your
suggestions.l