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

  • Thread starter Thread starter gimp
  • Start date Start date
G

gimp

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.
 
Nel post *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
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.
Click to expand...

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


--
(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
 
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?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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.
 
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.
 
Back
Top