to franz verga or any other (IF/OR) situation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gimp - 28 Jun 2006 03:23 GMT

i have posted on trying to get cell A1 to = a (*) based upon what ever
cell B1 says. thanks thru alls help i can. but trying to figure this
thing out has become more complicated that i thought. i wound up
realizing that the formula in A1 is limited. heres the situation. i do
employee scheduling for a company and the place is open 24/7. we can
not schedule anyone less than 3 hours, nor do we ever schedule anyone
over 9 hours. what i need is a formula that can take anything from mid
night to 9am that would read as 12am-9am in cell B1 and generate in
cell A1 a (*), as an example but in 1/2 hour incriments from 3 hours to
9 be it 3pm-9pm, 12pm-3pm,9am-12pm any variation from 12am-11:30pm to
generate a (*) if it says anything other to generate a blank cell.
there will be further questions as i develop. thanks in advance.

Reply to this Message

Franz Verga - 28 Jun 2006 10:24 GMT

Nel post news:1151461422.576124.207630@y41g2000cwy.googlegroups.com
*gimp* ha scritto:

> i have posted on trying to get cell A1 to = a (*) based upon what ever
> cell B1 says. thanks thru alls help i can. but trying to figure this
[quoted text clipped - 8 lines]
> to generate a (*) if it says anything other to generate a blank cell.
> there will be further questions as i develop. thanks in advance.

Maybe due to my poor English, but I don't understand what you mean.
It would be simpler if you could rpoduce a small example file of your
situation and desired goals and post it to www.savefile.com

Signature

(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Bob Phillips - 28 Jun 2006 10:31 GMT

I think this is none too clear.

Post some examples of what would go in B and what you want in A.

I assume you want this to happen as the value is entered in B?

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> i have posted on trying to get cell A1 to = a (*) based upon what ever
> cell B1 says. thanks thru alls help i can. but trying to figure this
[quoted text clipped - 8 lines]
> generate a (*) if it says anything other to generate a blank cell.
> there will be further questions as i develop. thanks in advance.

Reply to this Message

paul - 28 Jun 2006 12:16 GMT

i think it would be much easier for you if you used two cells for your input
of start and finish times and used 24 hr times.....However nothing...well
almost nothing is impossible.

Signature

paul
paul.shepherd@nospamparadise.net.nz
remove nospam for email addy!

Reply to this Message

JMB - 29 Jun 2006 01:46 GMT

I think I see what you're asking. If the difference between the start time
and end time is between 3 and 9 hours and is in a half hour increment, you
want an "*" otherwise blank. I would put the start/end times in different
cells, not 9am - 12pm in one cell.

If your start time is in cell B1, end time is in C1 and they are formatted
as Date and Time (like 6/28/06 2:00 PM), try:
=IF(AND((C1-B1)*24>=3,(C1-B1)*24<=9,ROUND(MOD((C1-B1)*24,0.5),2)=0),"*","")

If the start/end times are formatted as time (with no date such as 2:00 PM)
and you want to assume the end time is the next day if your times straddle
midnight (such as 9:00 PM to 12:30 AM the next day), I think this will work:
=IF(AND(((C1-B1)*24+(SIGN(C1-B1)<0)*24)>=3,((C1-B1)*24+(SIGN(C1-B1)<0)*24)<=9,ROUND(MOD(((C1-B1)*24+(SIGN(C1-B1)<0)*24),0.5),2)=0),"*","")

Although there may be a more elegant solution - I don't work with times a lot.

Reply to this Message