Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
to franz verga or any other (IF/OR) situation
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
to franz verga or any other (IF/OR) situation
Nel post oups.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 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. 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
to franz verga or any other (IF/OR) situation
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) "gimp" wrote in message oups.com... 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
to franz verga or any other (IF/OR) situation
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. "gimp" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it Possible? | Excel Worksheet Functions | |||
sumif to return an intersection? | Excel Worksheet Functions |