#### if between statement

I have a staffing spreadsheet and I need to have a multiple if
statement and I cannot figure it out.

I have A1 7:00 am, B2 4:00 PM  B3 & B4 Break start and stop times, B5
& B6 Lunch break times, B7 & B8 Break times.

I have the Following columns set in 15 minute increments.

I want to receive a value of a "1", if It falls between A1 & B1, but
blank if it falls between breaks and lunches.

Thank you

 0
coan.jim (2)
1/6/2011 8:24:43 PM
excel.newusers 15348 articles. 2 followers.

3 Replies
597 Views

Similar Articles

[PageSpeed] 9

On Jan 6, 12:24=A0pm, Jimbo <coan....@gmail.com> wrote:
> I have A1 7:00 am, B2 4:00 PM =A0B3 & B4 Break start and
> stop times, B5 & B6 Lunch break times, B7 & B8 Break times.
> I have the Following columns set in 15 minute increments.
> I want to receive a value of a "1", if It falls between
> A1 & B1, but blank if it falls between breaks and lunches.

If the time to compare is in C1:

=3DIF(OR(AND(A1<=3DC1,C1<=3DB3),AND(B4<=3DC1,C1<=3DB5),AND(B6<=3DC1,C1<=3DB=
7),
AND(B8<=3DC1,C1<=3DB2)),1,"")
 0
joeu2004 (766)
1/6/2011 8:39:41 PM
Errata....

On Jan 6, 12:39=A0pm, joeu2004 <joeu2...@hotmail.com> wrote:
> If the time to compare is in C1:
> =3DIF(OR(AND(A1<=3DC1,C1<=3DB3),AND(B4<=3DC1,C1<=3DB5),AND(B6<=3DC1,C1<=
=3DB7),
> AND(B8<=3DC1,C1<=3DB2)),1,"")

So that the formula can be copied down or across:

=3DIF(OR(AND(\$A\$1<=3DC1,C1<=3D\$B\$3),AND(\$B\$4<=3DC1,C1<=3D\$B\$5),
AND(\$B\$6<=3DC1,C1<=3D\$B\$7),AND(\$B\$8<=3DC1,C1<=3D\$B\$2)),1,"")

 0
joeu2004 (766)
1/6/2011 8:41:42 PM
Hi Sir,

Put this in Cell C2, then Copy accross the end-time of Work.

=IF(AND(C1>=\$B\$3,C1<=\$B\$4),"",IF(AND(C1>=\$B\$5,C1<=\$B\$6),"",IF(AND(C1>=
\$B\$7,C1<=\$B\$8),"",1)))

P.S. You are a lucky Staff due to you have 3 Times Break!!! LOL, hope
we also. Hehe!

Hope it will help!
~jaeson
 0
1/7/2011 8:20:20 PM

Similar Artilces:

"if" statements
I have a convoluted issue and I hope I can describe it clear. I have a form (frmCloseout) that is fed from a query (qryLink). In the design mode of the form, the "detail" area shows one line. When you run the report, all the information for that contract comes in and has multiple lines on the report view. Now I need to do some calculations to show up in the detail area of the form, but need to have my query recognize a specific cost code/cost type (the first two boxes in the detail line), and if this cost code/cost type is true, then perform a calculation that incl...

multiple "IF" statements in function
I'm trying to build a vacation tracker but i don't know VB. Is there a way to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) > (365) and <= (730) then (f) = "1 week" End If if (e) > (730) and <= (1825) then (f) = "2 Weeks" End If if (e) > (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) > (3650) Then (f) = &qu...

if statement #9
in col 2 , i have a if statement on col 1. i want the if statement in a code. Also the no of data on col 1 could vary. if there are blanks on col 1, i don't want a result on col 2 now after entering the data on col1, i drag the formula in the first cell on col2 all the way down to the last. Also the formula is open to all. I fear it may be tampered. In a code it maybe safe, right?? thanks rvik --- Message posted from http://www.ExcelForum.com/ Hi rvik! I'll try to help although your English is a little confusing. Try: =IF(A1="","",A1/2) If A1 is empty o...

I have a front/end and back/end MS Access databases that I am connecting via ADO. In the f/e I am issuing a query containing LIKE statement. The very same query works fine when issued in the b/e Access mdb. When placed in f/e VBA code, it does not work, there are no results returned, but no syntax error. I have a strong suspicion that the wildcard * may not work properly with ADO? My query is something like this: "Select field1, field2 from tbl where field2 LIKE '*blah*'" after successful DAO connection in VBA. There are no results. But there are results when the same que...

If statement in report
I am trying to put in an calculated field in my report. I have a field called "qualified" and in the field I would like it to total the number of "yes" fields. I have tried =iff([qualified] = "yes",count"yes") but that doesn't work. I think I am close. Does anyone have any suggestions. Are you actually trying to do this in the report or in the reports query? Is the field [qualified] actually a text field, or a Yes/No field? 1. If you are doing this in a query, and the [qualified] field is actually a Yes/No data type, then it will have to...

Using a COUNTIF statement with an AND operator
I'm using Excel 2003 so I hope I'm not limited here. We are performing a space usage survey and I need to compile the data results. Each location has 70 entries over the course of 10 days. I have a column for "group work" where the value is either 1 or 0. I have another column for # of people observed in a particular office. What I'm trying to do is the following: Get a count of instances where "# of people" = 1 and "group work" = 0 I'm doing a lot of countif statements, but I don't see how to do a countif with an and operato...

If statement in a Macro
Can a Macro have an if statement in it? I need to remove highlighting in a column if the value in a certain cell equals a certain value. Is this possible to do in a Macro? Thanks if range("a1")=4 then do your trick -- Don Guillett SalesAid Software donaldb@281.com "Dave" <Dave@discussions.microsoft.com> wrote in message news:71DFC5C5-254B-4F75-8794-2100D2E87377@microsoft.com... > Can a Macro have an if statement in it? > > I need to remove highlighting in a column if the value in a certain cell > equals a certain value. Is this possible to do in a ...

Option Grp and Field If statement
I apologize. I accidently posted this in the Modules forum first. I have a form called "frm_Credits". I have an option group called "Frame_Credit". Within this group are 4 options. They populate the "Credit" field. I also have a field called "Units". What I need to do is go through a series of scenarios that could occur and perform an action for each one. This is what I have for code and I put it after updating the "Units" field and also after updating the Option Group. After updating either one of these fields, nothing happens. ...

I can't connect to Schwab site today to directly download statement. I called Schwab to reset password and it is still not working. Error code OFXIE12031 keeps showing. This is first time I experience this problem. Heard you the first time. Let it rest for a few hours and see if it still happens. -- Joe Norton "fred" <fhe@chindex.com> wrote in message news:025c01c3783d\$2b206f70\$a001280a@phx.gbl... > I can't connect to Schwab site today to directly download > statement. I called Schwab to reset password and it is > still not working. Error code OFXIE...

Creating a dynamic IF statement
I have data in two sheets. I want to create an IF statement in the second sheet that has the ability to scan the first sheet for a value. If it finds a match to a value in the second sheet, deposit the value into the corresponding cells of sheet two. My example: Sheet 1 User B 12/10 User C 12/15 Sheet 2 User A User B In this example, I will have the IF statement in column B of Sheet 2. I want the IF statement to scan column A in sheet 1 for matches to column A of sheet 2. If it finds a match, place the corresponding value in column B (in this case, it wou...

Xpath equivalent to select in statement in sql
What I am trying to do is filter on some attributes and I could have a list of values, so instead of doing where attribute = value or attribute = value, I am wondering if there is something equivalent to the IN statement in sql, so attribute in (value1, value2) etc.. Thanks ...

Useing IF statements to change a cell color
I know this has been beat to death but I just cant seem to figure it out. This is what I want to do =IF(94<D8<97,[change cell color to red],[do nothing]) I have tried the conditional formatting but the value in D8 is referenced from another page in the same worksheet. How can I get this to work? Thanks. It will be a conditional format. A cf wants either a true or false answer. So for your example this formula should do =and(D8>94, D8<97) which will colour the cell based on your formatting choice when D8 is between 94 and 97... -- HTH... Jim Thomli...

Conditional Formatting and CountIF Statement
I need to apply shading to cells that have been counted in a countif statement. Help! Hi Kim! It would help if you gave us the condition for the countif But use that condition in Format > Conditional Formatting -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "Kim" <Kim@discussions.microsoft.com> wrote in message news:D0E7CEAC-A3C5-4E8F-B01F-E02A56F4D0DC@microsoft.com... >I need to apply shading to cells that have been counted in a countif >statement. > > Help! > > Thanks for helping me Norman. Here is the coun...

I've recently noticed that in several of my investment accounts, after reading my online statement, the 'account list' page still shows transactions to be read. In addition, in the acounts themselves, the 'To Do Read online statments' still show up. All my account information has been properly downlaoded and the accounts all balance, but I can't easily tell when there is new information present. When I click on the 'Read online statemnts, within one of the accounts, I'm taken to the 'Review investment holdings' page where my only option is to clic...

IF Statement 01-17-08
Hi, I am trying to run a query that has 4 fields: location, type, year, and cost. However I need to increase the cost for the year 2007 for 2 types. The IF statement I entered into the cost field is: TotalCost:IIF([Type]="C" Or "H" And [Year]=2007,[SumCost]/.9,[SumCost]) The issue I am having is that it is increasing the costs for all types. Is there a way to fix this or do i need a new query for C and H and then combine the 2 queries? Thanks for any help you can provide. Actually, you don't need to do it that way. Create a Where clause for the query tha...

IF statement?
I am compiling a 'build sheet' for a few products. I have part number in one column, and prices in another (with checkmark boxes near produc numbers). Im drawing a blank here, but I want to have the price appea in some box (have yet to determine, but I am going to put all th prices in a column, to make it easier to total). How would I go abou linking the check box, to the price? Hope that makes sense. Thank -- metalspe ----------------------------------------------------------------------- metalsped's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2282 Vi...

Sum if and statement
i am trying to put together a formula that sums a column based on the information in two other columns. The two columns that need to be evaluated have Dept name and Customer name. there are approximately 80 rows that need to be evaluated in each column, and i want the sum to be only of the values that match the criteria for the Dept & Customer names. assuming your data is in cells A2:C100, Dept Name is col A, Cust Name is col B, and value to sum is col C. =SUMPRODUCT(--(\$A\$2:\$A\$100=deptname),--(\$B\$2:\$B\$100=custname),(\$C\$2:\$C\$100)) Hope this helps. -- John C "Susan" ...

NESTED IF STATEMENT
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C85845.4A66D4E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I have to calculate commission on sales tax collected based on the = following criteria: 1) If sales tax collected is \$11.00 or less, then the commission =3D to = the sales tax collected 2) If sales tax collected is \$11.01 - \$333.33, then the commission=3D = \$11.00 3) If sales tax collected is more than \$333.33, then commission =3D 3.3% = of the sales tax collected, with a maximum amount =3D \$99...

IIF statement 08-23-07
I would like to use an IIF statement to look for the text in field [Type] and if it is not Voluntary, to show Employer on the report. I have tried IIF([Type] Not Like "Voluntary","Employer","Voluntary") and all I get is Employer listed on the report. It changes those that are voluntary to employer. Not sure what I am doing incorrectly. Karen Check Access HELP on "Like". Your IIF() expression tells Access to look for EXACTLY "Voluntary" in the [Type] field. If you meant "Voluntary" SOMEWHERE IN the [Type] field, you'd ne...

If statement referring to a blank cell
I want to put an if statement in code that will say something like If a1 is blank then blah blah blah end if I can not seem to figure out how to refer to "blank" papa Sub blank() If Range(A1").Value = "" Then MsgBox "blah, blah, blah" Else: MsgBox "halb, halb, halb" End If End Sub Gord Dibben Excel MVP On 16 Mar 2005 15:28:11 -0800, "papa jonah" <adullam04-excelgoogle@yahoo.com> wrote: >I want to put an if statement in code that will say something like > >If a1 is blank then > >blah blah blah > &...

Double If Statement help
Hi I am trying to create a formula that will look in column "b2" which will be a reference to a month i.e Jan then if the answer is Jan, then look in column "h2", to see if it contains a "1" (it will either contain a "1" or nothing). If it does i need it to take the value out of column G2 (which will be a number) and paste it in the cell im typing the formula into to. Hope This makes sense any help will be greatly appreciated. Thanks Submitted via EggHeadCafe - Software Developer Portal of Choice SharePoint Video Library Template Ava...

SQL Statement Problem
The following SQL Statement prompts for me to "Enter Parameter Value" for InstallDate, and I can't figure out why. If I leave out the Criteria, then it works fine. Can someone explain? Field: InstallDate: IIf([Forms]![boxSeats]![PrintWhich]=4,[TInstDate],[PInstDate]) Criteria: Is Not Null And IIf([Forms]![boxSeats]![PrintOneOrAll]="All Dates",[InstallDate]) Thanks, Bernie hi Bernie, bw wrote: > Field: InstallDate: > IIf([Forms]![boxSeats]![PrintWhich]=4,[TInstDate],[PInstDate]) > Criteria: Is Not Null And IIf([Forms]![boxSeats]![PrintOneOrAll]="A...

If statement help #6
I am trying to write a formula using multiple if statements or some other method in Excel and I cannot seem to make it work. Here is the scenario: If B9 > 0 then calculate value (E9) using (B9/.8) else if C9 > 0 then calculate value (E9) using (C9/.9) else E9. This is an insurance application dealing with coinsurance and conversion to 100%. Any ideas would be greatly appreciated. -- ekfinch ------------------------------------------------------------------------ ekfinch's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28658 View this thread: http://w...

Importing a CSV File and using a Conditional Statement and Row delete?
Hello Group, First off, this group is really helpful for us "newbies" attempting to do some calculations. A big kudo's for your monitoring this group. Question 1: I am importing a csv file into excel, and then need to do the following: 1) If on a row I have values in column A and B, excecute this calculation. 2. If #1 does not execute, then I should have values in columns C and D, and calculate this formula. Question 2: When I bring that csv file in, I have many blank rows that I would like to delete quickly. Any advice? 1. Use worksheet formula like this, =IF(AND(NOT(I...