excel if then statements with dates

Question:excel if then statements with dates

In an excel spreadsheet, I have two colums, each with a date in it. I want the third column to yield the greater of these two dates. For example, column one has 11/10/09, column two has 12/15/10, I want column three to show 12/15/10 as the greater of these two dates. Thanks.

Relevance
100%

Preferred Solution:
excel if then statements with dates

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

Hi,I am looking to write If statement that if I write a date into cell A2, then A1 will display "Good" if its within 4 years since the date in A2, or A1 will display "Expiring Soon" if its between 4-5 years after A2, or A1 will display "Expired" if its 5 years after the date in A2. I have never used If statements with dates before so I don't know where to start and any help will be greatly appreciated.Thanks,

Answer:How to If Statements Using Dates in Excel?

re: "if its within 4 years since the date in A2"By its I assume you mean today's date. If so...Take a look at the EDATE function. EDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date).e.g. For your 5 year (60 months) expiration criteria, this should work.=IF(EDATE(A2,60)<TODAY(),"Expired","")Basically what this says is "If I add 60 months to the date in A2 and the result is less than today's date, then the date in A2 must be more than 5 years ago."The "inverse" of that is to subtract 60 months from today's date to get the same result:=IF(EDATE(TODAY(),-60)>A2,"Expired","")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

How do I write an If/Then statement in excel to change data to red if it's 6 months old? I have one field for the current date, B2, and a column of dates, B6-B40, that I want to know if the dates are older than 6 mo from today's date. If they are older then 6 months from today's date, I want the dates in column B and their corresponding data in column A to turn red. Any ideas? Thanks in advance.

Answer:if then statements with dates in excel

See if this works for you:This is for Excel 20071) Select your cell or Range of Cells: B6-B402) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(G3,TODAY(),"M")>6=DATEDIF(B6,TODAY(),"M")>66) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKThis will highlight the cells that are GREATER THAN Six months.For Six Month OR GREATER use:=DATEDIF(G3,TODAY(),"M")>=6=DATEDIF(B6,TODAY(),"M")>=6EDITED, did change the cells from my test sheet.MIKEhttp://www.skeptic.com/

Hello,I am trying to do the following in Excel:If the date in column Q1, is 30 days over the date in column P1, outline or fill Q1 in a color. Is this an IF statement or conditional formatting?Thank you!

Answer:Excel if statements with dates

Conditional formatting is what your looking for:1) Select your cell, Q12) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =IF(P1+30<=Q1,TRUE,FALSE)6) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKMIKEhttp://www.skeptic.com/

I have an excel document. How can I get excel to automatically populate several fields based on a date in another field. For example: If field A5 has a date of 02/01/2011..how do I have Excel automatically generate fields C6 thru C14 with dates of 2/1 thru 2/8? The fields in C6 thru C14 would change based on the date entered in C5. Any help is greatly appreciated

Answer:If and Then Statements with Dates in Excel

In C6 enter this:=A5In C7, enter this and drag it down as far as you need:=C6+1Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I am working with an Excel file and I have today's date autopopulating in cell C1 using the NOW function. In cell b12 I enter the date of an instance, for example 4/15/2013, then in C12 I am using the function of IF(B12="","",SUM(B12+30)) to give me 30 days from the date. In cell H12 I am trying to have that cell autopopulate if the date in C12 is older than today's date in C1. I have written the formula the way I feel it should work, but it is not working yet. Here is what I have =IF(C12="","",IF(C12<C2,"","X")). Once I get that working there is another formula for me to add where it turns the line red if there is an X in H12. It seems like a lot of stipulations and connections and I know if one thing is off, then more than likely even more will not work.Thank you

Answer:Excel if then statements using dates

I don't see where you are using C2 for anything, so I can't answer any question related to IF(C12<C2,"","X").BTW, if you are only dealing with Dates, why not use =TODAY() instead of = NOW()?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

EX:Launch date = 2/16/2014List for someone to select = T+1. T-0, T-1 etc...T+1 = 7 days after the launch, T-1 = 7 days before the launch etc...If want a formula that will take the list selection (T-1) and then count back by 7 days from the launch date to populate a due date for that action item.

Answer:IF statements with dates

When you say "List for someone to select = T+1. T-0, T-1 etc..." do you mean a Data Validation Drop Down List?If so, something like this might work.Column A contains the values for your Drop Down ListColumn B contains your launch dateColumn C contains the actual Drop DownColumn D contains the formula shownFor T-2, the formula will return 2/2/2014. A B C D 1 T-2 2/16/2014 T-2 =RIGHT(C1,LEN(C1)-1)*7+B1 2 T-1 3 T+0 4 T+1 5 T+2 Note, if the "T" values will always be a single digit, then you can use this:=RIGHT(C1,2)*7+B1Using the LEN function as above allows for any value, such as 20, 137, etc., as long as the value doesn't result in an invalid date.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I am trying to create a timesheet spreadsheet (in Excel 2013) and I need it to identify if a specific date is a bank holiday and input "bank holiday" for example I have tried inputting=if(C16=04/05/2015,"Bank Holiday","")C16 is the starting date for the weekly timesheet.It only returns the "" bit even though I know 4th may 2015 is a bank holiday.Any help would be very welcome.message edited by euromadman

Answer:If statements for bank holiday dates

Excel is evaluating the 4/5/2015 as a number, not a date.To prove this to yourself, click on the cell containing the formula. then click on the Formulas tab, then "Evaluate Formula". Each time you click the Evaluate button, you can watch Excel perform each evaluation step. Eventually you will see 04/05/2015 replaced with 0.000397022332506203, which is not a date.Try this formula instead, so that Excel knows that you are looking for a date in C16:=IF(C16=DATEVALUE("4/5/2015"),"Bank Holiday","")Another option is to put a list of all of your holidays in a list then use ISNA and MATCH to see if the C16 Date is in that list.=IF(ISNA(MATCH(C16,$B$2:$B$10,0)),"","Bank Holiday")The ISNA function checks to see if the MATCH function found a match. If the MATCH function returns #N/A, it means that no match was found and ISNA will return TRUE. The IF function will use the TRUE to return an empty cell.Once again, you can use Evaluate Formula to follow the steps.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

Answer:How can I auto-populate dates in Excel based on other dates?

Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day. See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:http://www.cpearson.com/excel/datet...As for your question, simply subtract the number of Days required from the cell with the Goal Date.With 11/01/2016 in A1, use this to return 9/20/2016:=A1-42(42 is 6*7, or 6 weeks.)I'll leave the other two formulas to you. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Can someone help me with this formula.The statement works for the first part of the IF statement and displayes PM100, but not for the second... it only displays TRUE.=IF(IF('Master Project Data - INPUTS'!I3>=Sheet3!B1,1,0)+IF('Master Project Data - INPUTS'!I3<=Sheet3!C1,1,0)=2,"PM100",IF('Master Project Data - INPUTS'!K3>=Sheet3!B1,1,0)+IF('Master Project Data - INPUTS'!K3<=Sheet3!C1,1,0)=2)I want it to display PM300, but I get an error when I try to add it to the formula.

I have a list of 350 companies and I am trying to assign a label next to them from "small", "medium" or "large".Column B contains the name of the company, column G contains turnover value, column H contains the number of employees and column I contains the balance sheet total for the companies.There is a diagram I am following but trying to put it into formula is proving difficult. It is: Does the company meet 2 of the following criteria?: have turnover which is less than 5.6 million (cell Q4), balance sheet total less than 2.8 million (cell Q5), less than 50 (cell Q6) employees. If the answer is Yes, they are classified as "small", but if it is no, then...does the company meet 2 of the following criteria?: have turnover between 5.6 million (cell Q6) and 22.8 million (cell Q12), balance sheet total between 2.8 million (cell Q5) and 11.4 million (cell Q13), between 50 (cell Q6) and 250 (cell Q14) employees. If the answer is yes, they are classified as "medium" but if it is no, they are classified as "large" (when 2 out of the 3 values exceed the limit).I can create an If formula: =IF(OR(G2<=Q4,H2<=Q6,I2<=Q5),"SMALL","NO")but this means it is just giving the answer if just 1 of the criteria was met not 2.and this one: =IF(AND(G2<=Q4,H2<=Q6)*OR(G2<=Q4,I2<=Q5)*OR(H2<=Q6,I2<=Q5),"SMALL","NO")but I'm not sure if this is working because I want it to calculate if 2 out of 3 are less than the limit, e.g. there would be 3 combinations/permutations G2 and H2, ... Read more

Answer:Excel If Statements

Try this:It tests the "2 out 3" criteria for Small. If all of those fail, it tests the "2 out 3" criteria for Large. If all of them fail, it returns Medium.=IF(OR(AND(G2<Q4,H2<Q6),AND(G2<Q4,I2<Q5),AND(H2<Q6,I2<Q5)),"Small",IF(OR(AND(G2>Q12,H2>Q14),AND(G2>Q12,I2>Q13),AND(H2>Q14,I2>Q13)),"Large","Medium"))I've split formula into multiple lines to make it easier to read.

I need to write an IF statement with Excel to do the following:Take a value from a cell (say cell B2), see if this value occurs anywhere in column B on another worksheet and then, if it does leave the cell the formula is in blank and if it doesn't, put an * in the cell.Looking at the IF statements dialog box I can see how to set the blank cell or the *, but I don't know how to make it compare the value in B2 with a list of values in column B of another sheet. Can anyone help?

I need the following... having a hard time making it work.If A4=H3 AND the text in C4=pig, then show a 1 in H4. If A4 is blank, then show blank in H4.If A4=H3, but text in C4 is not=pig, then show blank in H4.Help?

Answer:If and And statements in Excel

Homework???:-)We do not support homework here.Use the help function of Excel for IF statements or use google for IF statement in Excel.

I have reviewed the formula but only the final FALSE statement validates. Below is the formula:=IF(AND(AD10="1",AF10="0.5"),"$145.00",IF(AND(AD10="1",AF10>"0.5"),(((AF10*2)*93)+15),IF(AND(AD10="0",AM10="1",AO10="0.5"),"$145.00",IF(AND(AD10="0",AM10="1",AO10>"0.5"),(((AO10*2)*93)+15),IF(AND(AD10="0",AM10="0",AX10="0.5"),"$145.00",(((AX10*2)*93)+15))))))What am I missing?

Answer:Excel If And Statements

I haven't tried to test your formula, but from just looking at it I would advise removing all the quotes around your numbers,IE: "145.00" and "0.5" The quotes tells Excel that it is TEXT not a Number.MIKEhttp://www.skeptic.com/

Calling all you excel experts, I will try and explain what I am trying to do. I want the value .25 to be entered automatically into say cell A1 if the value in say cell A2 is 1 or 2. If the value in A2 is greater than 2 then I want the value .40 to be entered automatically into cell A1.I hope this is clear, I have been fiddling with this all day but my knowledge of if statements is very limited.

Answer:excel if statements

Sorry made a mistake in the above for A2 please read B1 so it should read:I want the value .25 to be entered automatically into say cell A1 if the value in say cell B1 is 1 or 2. If the value in B1 is greater than 2 then I want the value .40 to be entered automatically into cell A1.

I have data that is date sensitive that I would like to create an "If Statement" that would fill in a cell with the word "Weekend" when the date is a weekend date and "Business" when the date corresponds to a business-day date. I've not had much luck so far, so any help would be much appreciated.

Is it possible to summarise this formula =MAX(IF(E13>0,E3,0),IF(F13>0,F3,0),IF(G13>0,G3,0)) Basically the range is going to run from column E to AB, looking for values in row 13, return the value in row 3 & then from those answers display the highest value. I'm using excel 2003 & its limited to 30 arguments (per function) which means so far i've had to do it over 3 cells & then in another cell return the max of those 3 cells, very frustratingThanks

Need some help with VLOOKUP and using more then one IF statement.I searched around but am still confused.Basically this is what I have:=IF(AND(ISBLANK(C9),G30<23000,VLOOKUP(B5,[Freight.xls]Freight!A2:C4,2,FALSE),IF(ISBLANK(C9),G30>23000,VLOOKUP(B5,[Freight.xls]Freight!A2:C4,3,FALSE)),VLOOKUP(B5,[Freight.xls]Freight!A2:E4,4,FALSE),VLOOKUP(B5,[Freight.xls]Freight!A2:E4,5,FALSE)),VLOOKUP(B5,[Freight.xls]Freight!A2:E4,5,FALSE))Or I need a way to use both these formulas:=IF(ISBLANK(C9),VLOOKUP(B5,[Freight.xls]Freight!A2:C4,2,FALSE),VLOOKUP(B5,[Freight.xls]Freight!A2:C4,3,FALSE)) =IF((G30<23000),VLOOKUP(B5,[Freight.xls]Freight!A2:C4,2,FALSE),VLOOKUP(B5,[Freight.xls]Freight!A2:D4,4,FALSE))Any help is greatly appreciated as I'm trying to use more then one variable I'm unsure of how to do it.Thanks,

Answer:Using Vlookup and IF/And Statements in Excel

Hi,From what I can gather from your formulas, you want to return one of four columns in the range A2:D4 on the freight worksheet, based on whether C9 is Blank or not and whether G30 is less than 23000 or not.The actual value returned from columns 2 to 4 in the range depends on the value in B5 (using the VLOOKUP() function).If I have this right, there are four initial conditions:C9 blank/G30<23000C9 not blank/G30<23000C9 blank/G30>=23000C9 not blank/G30>=23000C9 blank/G30<23000 returns column 2 data as far as I can see, but it is not clear which column to return for the other 3 combinations.Can you specify the columns for all four combinations.Regards

I have two spreadsheets that I need to gather information. If Column A number matches Column B number (on a different spreadsheet) then insert the value of Column E on the second spreadsheet. I think it will have to also include a VLookup

Answer:Excel - if/then statements with VLookup

Why do you think it require VLOOKUP?I don't think you do, but I'll admt that I don't I completely understand what you are trying to do.re: then insert the value of Column E on the second spreadsheet. Insert? Do you mean copy?...on the second spreadsheet? Do you mean it should end up on the second spreadsheet or should be retrieved from the second spreadsheet?Is this what you are looking for? This formula will return the value from Sheet2!E1 when Sheet1!A1 = Sheet2!B1=IF(Sheet1!A1=Sheet2!B1,Sheet2!E1,"")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have two formulas that work correctly separately, but I'd like to combine them into one formula if that's possible.

NEW FORMULA GOAL:

IF HUSBAND’S DEAD, TRANSFER 50% OF HIS PENSION TO WIFE PLUS WIFE’S PENSION.IF HUSBAND'S NOT DEAD, ENTER WIFE'S PENSION AMOUNT ONLY (COMBINE NEXT FORMULA)IF WIFE'S AGE >= HER PENSION START AGE, ENTER HER PENSION AMOUNT.IF WIFE’S AGE IS LESS THAN OR EQUAL TO HER PENSION START AGE, AND WIFE’S NOT DEAD, ENTER ZERO.

Where B6 is Husband's current age; B41 is year Husband dies; B18 is Husband's pension amount; B20 is % of Husband's pension his Wife will receive upon his death; D18 is Wife's pension amount.

Working Formula Two & Description:

IF WIFE'S AGE >= HER PENSION START AGE, ENTER HER PENSION AMOUNT.IF WIFE’S AGE IS LESS THAN OR EQUAL TO HER PENSION START AGE, AND WIFE’S NOT DEAD, ENTER ZERO.==================================================================IF(AND(C9>='DATA INPUT'!$D$19,C9<='DATA INPUT'!$D$41),'DATA INPUT'!$D$18,0)

How do you add if statements to formula in Excel that change the formating of the cells.

For instace:

If I have a sum which subtracts one cell from another and I want the colour of the text in the results cell to turn red if the value becomes negative.

And:

If I have a number in a cell and while the number is say 8 then that is fine so I want nothing to change, but if the number becomes say 3 then I want the colour of text in the cell to turn red, how would I go about it.

Regards

AF

Answer:If statements in Excel worksheets.

Man, I haven't had to try this in a long while. I remember it used to be a rule menu called "conditional formatting", but I've never tried it in newer versions of excel. Give me a minute and I try to reacquaint myself.

I need to sum a column IF two criteria are met.Basically, I need excel to look at column C to see if a cell holds "salary" AND column F to see if a cell holds "V". If both conditions are met I need the amount in column G to SUM.IF C5:C107="salary" AND F5:F107="v" THEN SUM G5:G107I have tried to nest AND in SUMIF and have failed with multiple tries.ThanksKeri

Answer:Excel: Nested IF/AND statements

There are probably other ways, but try this:=IF(AND(COUNTIF(C5:C107,"salary"),COUNTIF(F5:F107,"v" )),SUM(G5:G107),"")MIKEhttp://www.skeptic.com/

I have data in excel from columns A-F. In Column E, if there was an error it displayed a value, if the column is correct there was no value in Column E. How can I create a function (maybe an if statement?) that pulls the entire column if there is an error value in column E and copy's it into a new sheet in Excel. message edited by MelanieJ

Answer:complex IF statements in Excel

I'm confused.What does "if the Column is correct" mean? What does Column E have to do with the data in columns A:F?What column do you want to copy?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Can someone please tell me where my error is in this IF statment?=IF(AND(N3=1,O3<DATE(2010,7,6),1,IF(AND(N3="OUT",O3<DATE(2010,7,6),1,"Out"))))

Answer:nested if statements excel

Excel question are best asked in the Office Software forum,that being said, it looks like you did not close off your AND functions.Try this:=IF(AND(N3=1,O3<DATE(2010,7,6)),1,IF(AND(N3="OUT",O3<DATE(2010,7,6)),1,"Out"))))MIKEhttp://www.skeptic.com/

It keeps giving me an error after the 8th condition, is there anything I can do?

Answer:Excel Multiple IF Statements

Excel does not like more than 7 nested functions. You can use the change event for the worksheet. Open your worksheet, hit Alt + F11 to edit VB. Enter the Worksheet_Change sub, and the Changer sub below. Save and close VB.

I am unfamiliar with "complex" formulas in Excel. I am trying to create a nested if statement that has 11 possible results. my original data is numerical and my formula is to convert that number to a 'bracket" example: E14= 400000 result Up to 1 million etc.here is what I have been able to come up with to no avail. Any help would be greatly appreciated.IF(E14<=1000000,"Up to 1 Million",IF(AND(E14>1000000,E14<1500000),"1 - 1.5 Million",IF(AND(E14>1500000,E14<2000000),"1.5 - 2 Million",IF(AND(E14>2000000,E14<2500000),"2 - 2.5 Million",IF(AND(E14>2500000,E14<5000000),"2.5 - 5 Million",IF(AND(E14>5000000,E14<20000000),"5 - 20 Million",IF(AND(E14>20000000,E14<40000000),"20 - 40 Million",IF(AND(E14>40000000,E14<75000000),"50 - 75 Million",IF(AND(E14>75000000,E14<100000000),"75 - 100 Million",IF(AND(E14>100000000,E14<150000000),"100 - 150 Million",IF(E14>150000000,"Over 150 Million")))))))))))

Answer:Excel nested If statements

What version of Excel are you using?In 2000 & 2003 you are limited to 7 nested IF() functions2007 gives you 64, I think.MIKEhttp://www.skeptic.com/

How should I write an if statement for the folloing:IF 0<X<90 OR 180<X<270 then PRINT B. On the other hand, IF 90<X<180 OR 270<X<360 then PRINT S.Note that there are two contions for every case. If one of the two conditions in case one satisfies, then the output should B, The same for case two. Additionally, everything should be in one statement. I mean that I do not want to seperate the two cases in two cells.Thank you in advance.

I'm writing a Macro for Excel that takes data from one spreadsheet and presents it neatly (and printably) in another. Part of the information is the Status of the project, which is represented by a color (Green, Red, Yellow.) The first spreadsheet contains this information, but only in black and white. The second sheet pulls this information directly from the first in order to fill in the appropriate area.

My question is whether or not it would be possible for the second sheet to display the status in the color that it should be using an if than statement. The limitation I'm hitting is that the content of the cell is not "green" or "Yellow", but "=DynamicReport!A4" because it's pulling the color from the first sheet. Any thoughts?

I need to set up some conditional with data validation. If my value in A1 is "BillsFixed" I have a drop down list in B1 that provides all of the fixed bill options. However if in cell A1 "BillsVariable" is selected instead of BillsFixed I don't want a drop down menu to appear in cell B1, allowing the user to enter what the variable bill actually is. How is this possible?

Answer:Excel Data Validation IF statements

It is possible by using a WorksheetChange macro that will monitor A1 and add or delete the DropDown as required.See this thread, especially Response # 3 for example code.http://www.computing.net/answers/of... Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have 2 sheets - one sheet has customer names, the other sheet has customer names, date closed, dollar amounts and then a column stated closed. I want to be able to have when I mark closed on the client name for that dollar amount to be filled in on the first sheet. I have tried the following if statement, but it is not working=IF(A4=Forecast!$B$1:$B$15, TRUE, Forecast!$G$1:$G$14)

Answer:excel if statements multiple conditions

Keep in mind that we can't see your spreadsheets from where we're sitting, so we have no reference point from which to evaluate your formula.That said, that doesn't look like a typical IF statement that checks a cell for a value/string and returns a value based on what it finds.Please read the How To in the following line and then post some examples of your data and the expected results based on those examples.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

i want to make an IF statment in excel do more then one thing given a certain outcome.IE: IF(True, A1="this" A2="that",A3="THG" A4="Sucks")

so if truecel a1 says this, a2 says that

but how do i make it do both things what is the command that lets me do both. i was thinking & or something..

i want it to do THIS and THATis this doable?

Answer:stupid excel question Re: If Statements

I've never found a way to use an IF statement to alter the contents of ANOTHER cell. As I understand it, the IF statement (and related functions) exist only to return a result to that specific cell. I would love if there were an Excel guru who could correct me.

What you could do is just use different IF statments in each cell. Example:

Cell A1 = 1

In B1 =if(a1=1,"THIS","")In C1 =if(a1=1,"THAT","")

and so on through the cells that you want to set up. This is the only way to do it using the IF statement that I know of.

If you want to use macros, that'll work without a problem. You could throw a button on the page and have it pressed to return a result to as many cells as you want.

hello there,i need help as i need to rank 3 different groups from one data colum, i can separate the groups with and "IF" funtion and then i can rank those, however i need to have them in the same cell for working purposesName Group TotalAAB XY 256.22AAC XZ GHAG XX 241835.31JERHJE XY 103.27NEJ XZ NMEK XX 2725.87NMN XY 3071.4NMEK XZ 0.01KIUHJ XX 1110.45JNEL XY 2113.48NUE XZ 59723.16AAAS XX ENJKN XY 36755.94SRJR XZ 128750.99DEKE XX 5939.43EEK XY 48364.91UEJE XZ 24783.11BER XX 54323that needs to rank only within their groups.can this be done??thanks

I have set up a workbook for calculating permit fees based on the following:Area (where in the City you live)ITE Code (the type of building)The formula will ultimately reference a total of 11 worksheets (1 worksheet for each geographical area of the

city). The user will select the area from a pull down menu, look up the code and the formula then pulls the fee

information from the necessary worksheet. Everything works, but Excel will not allow me to exceed 7 IF

statements, so I am looking for another approach to the problem. Currently the formula (I am only showing

The cell C9 has a pulldown that allows only 1 of the 11 possible geographical areas within the city to be

chosen. The cell B9 contain the ITE Code. Each worksheet contains the same list of ITE codes. All ITE

codes are held in column A of each worksheet, hence the need to reference the sheet and range A11:A100.The E column of each worksheet contains the calculated fee value for a corresponding ITE code for that

geographical area. As each worksheet contains a series of predetermined values unique to that

geographical area, it is not possible to combine the worksheets. Besides, each year they are updated.Any help, I can get would be greatly appreciated.Thanks

Answer:Excel formula with multiple If statements

hi MAXC....

first of all....I don't think that Excel has maxed you on 7 IF statements.It's probably a case of your formula exceeding the maximum number of characters for the cell.

regardless.

you should be tying all of this to some Conditional Statements in a VBA routine. Do you work with macros/VBA at all?

All of these ranges of values that you mention can be handled much more efficiently and elegantly within code (not in-cell formulas).

I'm having trouble creating a nested if statement. I have to figure out how much bonus money is paid out to an employee based on their pay grade.Pay grades:Bonus Pay Grade 1 amount (located in cell Y2): $3,000Bonus Pay Grade 2 amount (located in cell Y3): $6,000Bonus Pay Grade 3 amount (located in cell Y4): $8,000The Bonus column is I2:I101This is what I have for my formula:IF((I2=1,Y2, IF(I2=2,Y3, IF(I2=3,Y4)))) The program doesn't seem to like it and I'm not sure why. Is it nested correctly? Where did I go wrong? Any help would be hugely appreciated. Thanks!!!!

Answer:Nested IF Statements Excel 2010

This appears to be homework. So I won't give you the answer but just ask:Why do you have a third =IF(), what's the logic?MIKEhttp://www.skeptic.com/

I am trying to create a formula using an IF statement that would go of a date. If this date falls between the first half of the month (1-15) then it would calculate storage price off of one rate, and if this date falls between the second half of the month (16-30/31) then it would calculate storage price off of another rate. If this possible?

I have a spreadsheet with report dates that need to be renewed annually. I'd like to format the date cell to turn red when the date is within 30 days of its annual expiration. For example, a report was last completed on 1/1/2012 thus cell C5 = 1/1/2012. I would like C5 to turn red on and after when the current date is 12/2/2012 (I have the current date in cell C26).Thanks!

Answer:Conditional Formatting and IF/THEN Statements in Excel

Try this:Conditional Formatting 20071) Select your cell or range of cells, IE C52) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(C5,TODAY(),"D")<=3966) Click on the Format button7) Select the Fill Tab8) Select a Red color9) Click OK10) Click OKSee how that works.MIKEhttp://www.skeptic.com/

I am trying to calculate a complicated commission. Here is what I was trying that is not working. =IF(and(I7="N",(F70.016)<6000),F7.016,or(I7="N",(F70.016)>=6000), 6000, or(I7="Y",(F70.016)<6000),F7.016, or(I7="Y",(F70.016)>=6000, 5062.5))I7 - determines the pay structure (if = to "N" the max commission is 6000, if = to "Y" max commission is 5062.5)F7 - is the sale price used to determine commissionIf I7="N" and F70.016 is less than 6000, the commission is F70.016If I7="N" and F7*0.016 is equal to or greater than 6000, the commission is 6000 If I7="Y" and F70.016 is less than 6000, the commission is F70.016If I7="Y" and F7*0.016 is equal to or greater than 6000, the commission is 5062.5Can anyone help?

Answer:Excel multiple if statements for commission

IMHO, this seems far too complicated to achieve all in one go. In fact, I'm having difficulty understanding what you are trying to achieve. Its not clear whether you are doing this in Excel or Access. You should get away with it in Access, if you can get it right, but your documentation for future updating will need careful writing. In Excel ... !!!I recommend that you take it one step at a time. Use interim stages to show what you have achieved to date. In Excel, you can always hide the interim columns once you have your work completed, and in Acess, the interim stages may well have a use later.

Hi, I'm trying to figure out how to do a SUMIF/S formula that only adds cells that are numbers but not cells that are dates. Is there any way to do this? Thanks

Answer:Excel If Statements using cell format

Not knowing what your numbers and/or dates look like, it's hard to give a specific answer.For example, if all of your dates were equal to or later than 1/1/2014 and all of your numbers were less than 41640, you could use this:=SUMIF(A1:A10,"<41640")Since 1/1/2014 is stored internally as 41640, it can be treated just like any other number as far as the SUMIF function is concerned.If we had some specifics about your data, perhaps we could offer something to fit your exact needs.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

in excel on a if statement i have two columns of data that contain either pass or fail and i want to take those 2 columns and make them into one with if 1 of the 2 colums contains a fail then "fail" on my one column. otherwise a "pass".

Answer:help on excel multi column if statements

You might get more help at the Microsoft Office forum. Post here.Frank Pepper: ?Professionals built the Titanic, amateurs the ark?

I have a spreadsheet for tuitions for a small non-profit org. The parents are supposed to pay $75/week for a child. However, some leave different amounts every week, sometimes more, but usually less. Also, some students have not started yet and therefore would have an "x" in their cell for that week. If a parent has not paid at all, I leave it blank (they are easier to find). Anyway, I would like to COUNT the cells with either a number ">0" and "x" for each column. I can do either or but not and....can anyone help?

I would make it so that it counts the empty cells, but when i'm entering i sometimes put a space and in that case, i won't get a correct count!

Thanks so much for any help you may be able to provide!!!!

Answer:Excel Countif Formulas (multiple if statements)

i figured it out...but thanks anyway.

FYI.....i just put two countif statements in and a + sign between them.

Here is the scenario...I need Col H to look up the value in Col G and tell me the result (Green, yellow, red) based on the ranges.

My end result needs to be colored cells (green, red, yellow) with the corresponding color as text in the cell (so a green shaded box would have the text "Green" displayed)

How can I do that? Please help!Thank you so much!

Answer:If/and statements or conditional formatting (MS Excel 2007)

The result of your lookup can give you the color in wordIf range(?????) + 1 then the contents are the word "green" or "yellow" or "' red"with conditional formatting if value = "red" choose for pattern color red , etc.I hope this gives you a hint in the right direction

Is it possible to build several if statements that return multiple values if satisified to one cell in excel. For instance, I have the following Info in Excel:Cell A1: Coborrower Cell B1: John DoeCell A2: Principal Cell B2: Jane DoeCell A3: Coborrower Cell B3: John SmithCell A4: Coborrower Cell B4: Jane SmithCell A5: Guarantor Cell B5: Jack DoeIn Cell G6, I want to create an if statement that says if coborrower is found in cell A1, A2, A3, A4, or A5 then return the corresponding values of the B Column seperated by commas. For instance, in the scenario above I would want G6 to read: John Doe, John Smith, Jane Smith

Answer:If Statements Returning Multiple Values Excel

The result listed above does not have to be seperated by commas but would be preferred.

Hi. I have an excel spreadsheet that is currently showing all open orders per retailers. The last 6 columns show: Todays date, and then the next working day, next working day, etc. The last column is anything after the "fourth" date past todays date. So below the dates I have x's that are to fill themselves in, regarding the correct dates. For example, I have an order that was received on 6/30/17, so the x would fall under the forth working day (not including the day it was received). Therefore the ship date would be 7/7 due to the holiday this week. Can someone please help me with the crazy excel formula that I need to concoct to have the x's fall into the correct dates? I'm struggling.

I am using Office 365 Excel 2013, but we do not have access to the Cloud features. I think we are onWindows 7.

What I am trying to do is create a spreadsheet for our managers to check off when a task has been completed. When they check the forms control box, the forms control box in B17 is assigned to say M17 and the word TRUE populates M17. The other form boxes are relative to the "results" cell. (Note if there is not check in the forms box then the "results" cell is either FALSE or is blank). Once all the boxes are checked, I want to change the cell color of A16 (title Accounts Payable) to green and generate an email notifying me saying Accounts Payable tasks are complete.

Here is a sample - I have also uploaded a copy of the excel document.

I realize that the email being sent out takes VBA programing and I think I have an example of this but haven't tried it yet. Is what I want to do possible? Is there a better way to go about doing this?

Thank you for the help.

Answer:Excel count true statements then send out an email

Are you still looking for a solution to this?

It seems possible. If you have any code (even if it's not working), please share it. Additionally, it sounds like the real bulk of the code is going to come from sending an email. The rest of it pretty much seems done or seems like one line of code.

How do you plan/want the email to be sent? What email program are you currently using on your computer? I would assume Outlook since you have Office, but not sure.

It also seems like other people are accessing this file. Is it stored on a network folder and everyone accesses it that way?

I need a result of no more that a 6. So 7 hours 29 minutes should return a 6 and 7 hours and 30 minutes returns a 6. However, a 5 hours and 29 minutes should return 5 and 5 hours and 30 miutes or more returns a 6. So 0-29 minutes rounds down and 30-60 rounds up to the next hour. Im using Excel 2013.

For some reason as I have added dates to cells, they are not getting converted to the Date type I have selected: 15/03/2009I type in my entries in this format:March 15, 2009 (usually it converts immediately but not this time)I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to 03-15-2104Any suggestions?

Answer:converting excel dates in Excel 2003

Dat's cuz they're probably not being recognized as dates by Excel. My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.Try this with one of your stubborn "dates"...let's assume it's in A1=DATEVALUE(A1)Now format that cell anyway you want.Then do a Copy...PasteSpecial...Values to eliminate the formula.

I have been using this formula to let clients know when their bills are due: =DATE(YEAR(A1),MONTH(A1),DAY(A1)+90)this ends up looking something like this: 1/1/04however, I have had a request from them to have it look more like this: 1-Jan-2005.Any help would be greatly appreciated. Thank you.

I have a sheet in excel which I input my daily results. Some dates or blocks of dates are missing as I am unable to gather the data. (vacation, travel, etc)Column A is the date (mm/dd/yyyy)Column B is the result (0.00)I have another sheet with the following:Column A has a list of all dates (month and day only) (366 rows) Columns B through M have the year in row 1I need to lookup the month and the day and attach the year based on the column year (this is easy)=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2)BUT I need the exact date data returned. VLOOKUP returns the closest lower value.If no exact date then return 0 or "".Is there an easy way to do this in Excel without writing code?Thanks for helping in advance!

Answer:Dates and Excel

If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookup argument:=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)Review the VLOOKUP Help file to see the options for this argument.If an exact match isn't found, this will return #N/A, so wrap the formula in an IF(ISNA()) function to get a "" of 0 if the VLOOKUP evaluates to #N/A. This will return "":=IF(ISNA(VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)),"",VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0))

I have a column of calendar dates (A1:A100) that do not include Saturdays or Sundays. I have certain production times for various products that I must base off of these dates and am having trouble counting an amount of days that doesn't include any Saturdays or Sundays in my count. Example below.

10-02-2006 I need to add 18 production days to this date. Workdays only being Mondays-Fridays, I need the answer of the formula to equal 10-25-2006.

Eldest daughter driving me mad.At business she is working on a database in Excel.She tries to shorten the DOB entry as for example23/11/29 for someone born in 1929 but it comes out as 23/11/2029 yet when she enters 23/11/31 it comes out as she wants it as 23/11/1931. Is there something we need to format in Cells/Date?Brambles

Answer:Excel Dates

and have the same problem! Using excell 2000 and my cells are formatted correctly. Very strange, however there are lots of very helpful people on this site and here's hoping.Taffy

I'm trying to set up a spreadsheet so that each time I open it, the time between two dates is calculated. The two dates would be today (the day I open the spreadsheet) and a date about 10 years away. Any help would be appreciated.ThanxDD

I've got a strange one, I think !!!Current date is 16 Jan 2005, and in my module I say Dim CurrDate As LongCurrDate = Format(Now(), "00000")This sets CurrDate as 38369Later in the module I want to compare this date with one from a cell ...Dim MyNewDate As LongMyNewDate = Range("N" & MyStoreRow) ... value is 17 Jan 2005The value being returned in MyNewDate is 38369, not, as I would expect, 38370 ...In another worksheet, the new date in Nxx is 18 Jan 2005, and this returns a value of 38370, not 38371 ... Any ideas as to what's happening here ?!?! Is my Current Date wrong ? Is this a known 'problem' ?!?!?

Answer:Dates in Excel VBA (again !) ...

I'm not sure what is goung on but you would be better off working with Dates rather than LongsSub test()Dim mydate As Datemydate = Now()MsgBox mydateEnd Sub

Hi All,I am updating a old spreadsheet and wanted to add some work days down the side of it. The problem I have is that I only want Mon-Friday dates to be listed. For example....12/3/201212/4/201212/5/201212/6/201212/7/201212/10/2012etc.....For some reason I can't get it to work right. In this example you can see that the first work day for Dec is the 3rd and that the 8th and 9th are passed over because they are a weekend. I know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Anyone have any ideas?ThanksSpoiler

Answer:Excel and dates

Quote from: Spoiler on December 14, 2012, 10:10:41 AMI know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Are dates for all rows part of the existing data? If so, then one approach might be to add a column to show the day of the week and then use a Filter to show all days except Saturday and Sunday.

Hello, What a great help this forum is.I know nothing at all about VBA, yet managed to get a macro in excel that allows me to send the "due date" emails that works almost perfectly for my needs. I based my macro on the code found here https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/But i'm still missing one function in this macro for it to be perfect for my needs. I want it to send only one email to the "area manager" specifying all the items (serial number and location) that follow my due-date rule (instead of one email per item). I want it to send a list of items that are due this month for example. Is it possible?

A guy phones in a fault. The date and time are recorded. When the fault is fixed, the date and time are recorded again. I need to calculate the response time, but I need it to only look at business hours, which are 8 to 5, Monday through Friday, when calculating this. Some faults may stretch over more than one working day, or over a weekend.

Is there a simple way to do this?

Thanks in advance for any help.

Answer:Excel, dates and times

I think you'll have to Google for this

I can suggest the following link for starters:http://www.cpearson.com/Excel/datetime.htm#WorkHours

I am having problems working on a spreadsheet of dates and am looking for help. I am creating a file to show membership ages and senority in our area. Here is my setup.The first column: "Members name". Second column: "Date of birth". Third column: DOB converted to automatically updated "Age" by Year and Month with "Datedif". Fourth column: "Hire date". Fifth column: Hire date automatically converted to "Length of Senority" by Year and Month with "datedif".I want to get the totals and averages of the Age and Senority columns. I think I have the averages worked out by using =AVERAGE(B2:B49) and then converting that with =DATEDIF(B53,TODAY(),"Y")&" Yr, "&DATEDIF(B53,TODAY(),"YM")&" M". But I am not having any luck with the totals.Can anyone help?

Answer:Working with dates in Excel

It is rather difficult to visualise this.Could you upload the file (with the members names removed) to a site like click here and provide a link to download it.

Does Excel (version 9.0) have a feature which will allow me to compute various day/month/year time lapses? For instance can I compute the number of months between Oct. 1, 2003 and September 31, 2013? Or if I want to determine what day it is 25 years from today? I can do this on my handheld, but would like to design an Excel formula for it. Any help would be appreciated.

Answer:Computing Dates in Excel

Use the datedif function

i.e. =DATEDIF(Date1,Date2,"Interval")

Where Date1 is Start DateDate2 is the end dateInterval is one of the following: You must use the inverted commas

"m" Months The number of complete months between Date1 and Date2.

"d" Days The number of days between Date1 and Date2.

"y" Years The number of complete years between Date1 and Date2.

"ym" Months Excluding Year The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months And Years The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

I am trying to do the following:If the date is before 1/1/2014 in cell I15 I want cell J15 to say "yes". If the date is after 1/1/2014 in cell I15 I want cell J15 to say "no".Then, in another cell, I want if cell I15 is between 1/1/2014 and 1/1/2015 I want the value in G15 to be placed in cell K15.

Answer:If Statement in Excel with Dates

For the J15 formula, what do you want to return if the date in I15 is equal to 1/1/2014?The same question holds for the formula in K15: What if the date equals one of the 2 dates that you are checking for?BTW, the solution will be based on the IF function. Have you read anything on the IF function in the Excel Help files or via a Goolge search? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

HeyI have imported data from an outside database and it brought in the dates seperated. The information is in columns and the day, month (which is in text), and year each have their own cell. There are hundreds of dates. Due to space considerations I need each date to only take one cell. Is there any way to do this without having to do it by hand?

Answer:Solved: Dates in Excel

Not sure which version of Excel you are using, but am assuming 2007. I will also mention what to do if you are using 2003 or earlier

Create a blank worksheet and do the following so that you can see how the following formula works and then how to convert it as a value rather than a formula

I want to make a new column of months from a date I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYYI then in column B use the month function - so = month(A1)it displays 12 - and if I go onto the Fx to see the expression helper / wizard I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

so that i format the =month(a1) into a MMM-YY format and get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

But what I'm after and maybe a simpler way is to have a dropdown on my pivot table of MM-YYYY from my date field rather than DD-MMM-YYYY

so I can choose and show month data

I'm sure I did this in the past just by using format - MMM-YYYY on a date field and it worked in the picot table OK

I want to have an IF true/false statement stating that for a column of DATES, if the date is before "5/1/2013" TRUE and everything else false within the if statement. thank you in advance!

Answer:How do a lay out an EXCEL IF statement using DATES?

Try this:With your column of DATES in column A,put this formula in cell B1 and drag down.=IF(A1<DATE(2013,5,1),"True","False")What happens if the date is exactly 5/1/2013?See how that works for you.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

I am trying to subtract two dates in excel but I keep getting weird results...

My dates are formatted like this: 8/11/2010

I am using TODAY() to return the current date. Then I want to compare that to see how many days have passed since a given date. So my formula is this: =TODAY()-D2

Now, it seems to kind of work because the result I get is 1/30/1911. 30 days is the correct answer! But why am I getting the full date?? How can I fix this? And why the heck is it saying 1911 - on another computer I tried it says 1/30/1900...

??

Answer:subtracting dates in excel... help please!

Look into the Datediff function. I think for what you want (doing this from the top of my head) is Datediff("d", D2, TODAY()) or you can sub TODAY() for NOW(). Like I said it is from the top of my head but should be something like that.

Forgotten how to format dates in excel so they accumulate down the column.e.g. has to be like this down the column1st January 1950, 1st February 1951, 1st March 1950 .... 1st January 2006 help much appreciated. Durko

Answer:Excel and Formatting Dates

Should it be 1st March 1952? If so:Tools > Add-ins, make sure Analysis Toolpack is ticked.With the first date in A1 enter in A2=EDATE(A1,13)and copy down as far as needed. Select Column A, Format > Cells and set to display as you wish.

Some times I have only the year (eg 1914). Other times I have the full date (eg 12/25/2011). If I format the Excel for as a date and enter merely 1914, it shows as 3/28/1905 (its reading only the "1914"). I do NOT want to enter 1/1/1914 - because that is an incorrect date (all I have is the year 1914). What I want: If I have only the year, I want only the year (eg 1914) to show. If I have the full date, I want the full date (eg 12/25/2011) to show. How do I do it? Does it require an IF statement?

Answer:Formatting Dates in Excel

Not sure what it is your after, but Excel stores dates and times as a number, representing the number of days since January 1, 1900So when you enter 1914, Excel reads it as 1,914 days since 01/01/1900or 03/28/1905.See this page for a complete explanation of how Excel works with Dates/Times:http://www.cpearson.com/excel/datet...MIKEhttp://www.skeptic.com/

Good day i would like to ask how to make an if statement with datesit would appear like thisa1=any date depending on expected deliveryb1=date receivedc1=either "Delayed" or "Ontime"IF A1<=b1 then c1="Ontime" else c1="Delayed" Endsomething like this for excel..

Answer:Excel coding using if for dates

Here is what you asked for:IF A1<=b1 then c1="Ontime" else c1="Delayed" EndThe syntax of an IF function is =IF(logical-test, value-if-true, value-if-false)=IF(A1<=B1,"Ontime","Delayed")However, I have a question.If A1 is the expected delivery date, and it is less than the date received, isn't the package Delayed?What am I missing?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dates input to Excel are being treated simply as a text string and not recognised as dates. Apparently, all was OK until recently (this is a friend's problem). Old spreadsheets suffer the same problem, so it seems to be a global problem.Any ideas please?

Answer:Excel not recognising dates

The cells have to be formatted to accept dates rather than text or numbers. Goto Format/cells after selecting those that need to be dates.

This should be simple and I thought I had it nailed, but the formula is not working, so I am obviously doing something wrong.I have two adjacent columns. The first column is always populated with a date and the second column may be populated with a date or may be blank. I am trying to determine if one or both dates are greater than 3/1/2008, then the row is "OK"; if neither date (or if the second column is blank) is before 3/1/2008, then that row is out. Here's what I tried to use: =IF(OR(H2>DATEVALUE("03/01/2008"),I2>DATEVALUE("03/01/2008")),"OK""OUT").The formula just does not return the correct result. Thanks for the help.

Answer:nested if excel with or and dates

You are missing a comma between "OK" and "OUT"Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have two columns of dates - a Due Date and a Completed Date - I want to compare the dates in each column and have the results appear in a table I have created on a separate sheet in the same workbook. If the completed date is after the due date for a certain type of information (contained in another column; ie, IMAC or Out of Scope), I want to count that instance as "Missed" in my table. Any help would be greatly appreciated!

Answer:Need help comparing dates in Excel.

I don't quite understand how the "IMAC or Out of Scope" information relates to your question.If all you are trying to do is compare 2 values (dates) a simple IF function should do it for you:=IF(A1>B1, "Missed", "")This will return Missed when A1 is greater than B1 and return a blank cell if not.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.