#3 вЂ“ D (Days): D used in a formula to find the number of days between the beginning date and end date.#2 вЂ“ M (Months): used in a formula to find the number of complete months between the dates.#1 вЂ“ Y (Years): Y used in a formula to find the number of complete years between beginning and end dates.End_date вЂ“ the ending date of the period.Unit: The time unit to utilize while ascertaining the difference between two dates.Start_date вЂ“ the initial date of the period you want to calculate.Check out these other Excel formula tutorials.DATEDIf is a date function used to find the difference between two given dates, now these differences can be in years months or days, it takes two dates as an arguments and one keyword to specify which type of difference is desired for the output, the output for this function is integer and the method to use this function is as follows =DATEDIF(Start Date, End Date, Unit), however, this function is compatible with excel 2000 and previous versions of excel. I hope you found these nested IF alternatives useful. It checks the index value in cell A9 and then returns the relevant information from its list. Or we could use this awesome and simple little CHOOSE function. This is the nested IF from cell D11 which returns the name of that leagues top goal scorer. Now in the cells mentioned above we could use a nested IF like below. A selection from the 4 leagues in the combo box will produce index number 1, 2, 3 or 4 in cell A9.ДЇormulas are in cells D9 and range D11:E15 returning the correct data for the chart from the combo box selection. A combo box is on the left and is linked to cell A9. The first 7 rows have data of the top goal scorers from 4 different football leagues in the 2016-17 season. It is all on one sheet here to get a better idea of how it works. Typically some of this data would be hidden or stored on separate sheets. So in this example I have an interactive chart being driven from a combo box control. And that makes it perfect for use with form controls. This function will perform an action based on a specific index number. Many Excel users will never have even heard of the CHOOSE function. This last alternative to the nested IF formula is a bit of a secret function. This is a range lookup, so it is essential that the first column of the lookup table (column E) is in ascending order. With the lookup table set up in range E4:F9 we could use this VLOOKUP function. And unless your trying to impress someone with big complex looking functions, there are better ways. Take the example below where we have a list of exam scores and we need to assign a grade for each score.ДЄ nested IF approach could look like this. In this alternative formula we use VLOOKUP again, but this time for a range lookup. There is no need for someone to have to edit the formula, which you or your colleagues may not be comfortable doing 10 months from now. If a membership price changes, you can just change the lookup table. This is very compact and easier to adapt in the future. In this example I will use VLOOKUP.Д«y creating a lookup table (F4:G7 below) we are able to use the following VLOOKUP function to search for each membership and return the correct price. Now you can use any lookup formula that you like to achieve this. I believe an even better alternative to the IFS function for this scenario would be a lookup. The IFS function tests cell B2 for each membership type and applies the correct price. There are four types of membership (platinum, gold, silver and bronze). In the example below we used an IFS function to calculate the price for each membership type. You can avoid all of those brackets that come with opening and closing multiple IF functions. This function was introduced to condense and simplify the task of writing nested IFs. Lets start with a function that is new from Excel 2016 called the IFS function. This blog post explores 4 alternatives which are easier, faster and cleaner than the classic nested IF. Nested IF formulas are extremely useful for complex decision making on a spreadsheet, but they can also be long, messy and convoluted.

Datedif not in excel 2016