I'm trying to prepare a simple sheet to be used as a schedule for a
department store. This store must close with at lease 5 people on
staff. I'm attempting to create a cell that checks all the employee
times for the day and tell me if there are enough people scheduled for
the store close that day. The same person makes the schedule every
week, and always types them like this "6:30-9" with 9 being a closing
schedule.

I'd like to develop a formula that will check a cell for a value of
"XXXXX-9" where the X's can be wildcards. All I want is the formula to
look at the last 2 characters of the cell and determine if they are a
"-9" and return a TRUE/FALSE result. The second thing I'd like to do
is to check a column for all these TRUE/FALSE results and determine if
there are equal/greater than 5.

I was asked by a friend to look into this because I have minor
experience in excel formulas, but this is way beyond my ability. All
he knows is how to punch numbers in a cell and make them pretty
colors :)

All your help is appreciated in advance!
Chuck

One way:

=COUNTIF(B:B,"*-9")=5

In article .com,
"Woodchuck" wrote:

I'm trying to prepare a simple sheet to be used as a schedule for a
department store. This store must close with at lease 5 people on
staff. I'm attempting to create a cell that checks all the employee
times for the day and tell me if there are enough people scheduled for
the store close that day. The same person makes the schedule every
week, and always types them like this "6:30-9" with 9 being a closing
schedule.

I'd like to develop a formula that will check a cell for a value of
"XXXXX-9" where the X's can be wildcards. All I want is the formula to
look at the last 2 characters of the cell and determine if they are a
"-9" and return a TRUE/FALSE result. The second thing I'd like to do
is to check a column for all these TRUE/FALSE results and determine if
there are equal/greater than 5.

I was asked by a friend to look into this because I have minor
experience in excel formulas, but this is way beyond my ability. All
he knows is how to punch numbers in a cell and make them pretty
colors :)

All your help is appreciated in advance!
Chuck

Hi Woodchuck,

Looking at your original post, I get the impression that you want to see at
a glance whether each member of staff is working til 9? If you just want the
result, JE McGimpsey's solution above works perfectly.

Otherwise...

Assuming you have the titles in Row 1, staff name in col A, the times in
format as described in col B, then enter in Col C2:

=IF(RIGHT(B2,1)="9",TRUE,FALSE)

Which will return TRUE for a 9 finish, FALSE for anything else.

For your "summary" to confirm that you have 5 people at 9pm, use in your
selected cell:

=IF(COUNTIF(C:C,TRUE)4,TRUE,FALSE)

which will return TRUE if 5 or more 9pm finishers are present, FALSE if 4 or
less.

HTH
DS

"Woodchuck" wrote:

I'm trying to prepare a simple sheet to be used as a schedule for a
department store. This store must close with at lease 5 people on
staff. I'm attempting to create a cell that checks all the employee
times for the day and tell me if there are enough people scheduled for
the store close that day. The same person makes the schedule every
week, and always types them like this "6:30-9" with 9 being a closing
schedule.

I'd like to develop a formula that will check a cell for a value of
"XXXXX-9" where the X's can be wildcards. All I want is the formula to
look at the last 2 characters of the cell and determine if they are a
"-9" and return a TRUE/FALSE result. The second thing I'd like to do
is to check a column for all these TRUE/FALSE results and determine if
there are equal/greater than 5.

I was asked by a friend to look into this because I have minor
experience in excel formulas, but this is way beyond my ability. All
he knows is how to punch numbers in a cell and make them pretty
colors :)

All your help is appreciated in advance!
Chuck

Thanks alot guys... I'll tinker with this a bit and see if I can
figure out the correct context in which to use these formulas. I think
I understand them well enough to adapt them properly to the sheet that
my friend sent to me. I'll post back if I need any more hints.

Chuck

On Mar 30, 2:29 am, "Woodchuck" wrote:
Thanks alot guys... I'll tinker with this a bit and see if I can
figure out the correct context in which to use these formulas. I think
I understand them well enough to adapt them properly to the sheet that
my friend sent to me. I'll post back if I need any more hints.

Chuck

Note to all who helped - The sheet is finished and works great. Thanks
for the guidance!

Chuck