Technology Training

Microsoft Excel 2016 Date and Time EDATE functions

In our last video, we showed how Microsoft Excel 2016 uses date and time functions. Now that you know the logic Microsoft uses to work with dates and times, you can use a variety of formulas to help make your job easier.

In this video, we will focus on a few more useful formulas that are the most common. Suppose you are creating a spreadsheet for all of your existing service contracts, and you want to keep track of when your contracts started and when they will end. Using the “EDATE” function you can tell Excel to add or subtract any number of days, weeks, months or years after a given date to produce the correct contract end date.

For example, if you are looking to find out when your contract will end one year from the start date, you would use the formula =EDATE(cell,12), which will add 12 months to the referenced date. Subsequently, if you have a five-year contract, you can multiply twelve months by five to get the correct date. That formula would be written as =EDATE(cell,5*12).

This formula is useful when calculating time in hours, minutes and seconds as well. Remember that dates are stored as whole numbers in Excel meaning that if you want to add 12 hours to a date, you would calculate the hours after the decimal point as 0.5.

There are countless ways to use these formulas and there are just as many applications that call for their use. Understanding the basic principles of how Excel uses dates and times will allow you to apply these formulas with ease. Once you get the hang of it, you can go to Microsoft’s support website to find many other useful functions.

Technology Training

Microsoft Excel 2016 Training “IF” Formula with “NOT” and “OR” Operators

In the last two Microsoft Excel training videos, we focused on using the “IF” formula, its Boolean operators and how to nest an IF statement inside of another IF statement. Today we will finalize our training on Excel IF formulas by reviewing how to use “NOT” and “OR” operators.

The NOT and OR operators are among the most common operators you will use in the IF formula. These operators allow you to create equations that will say “IF a workbook cell is NOT equal to X OR Y, then perform XYZ action.”

Let’s suppose you are needing to create a spreadsheet and you want a logic formula to adjust the prices of your products up or down according to their sales performance. An example of this would be if your yellow and pink products are not selling and you want to increase the price for the rest of your products while keeping the yellow and pink products the same price. These operators would allow you to say “IF the product is not Yellow or Pink, then increase the price.”

There are many ways to use these operators, and if you combine them with the operators that we reviewed in previous videos, you will find the IF formula to be quite powerful. With some practice, you will find that these operators are able to help you make most any calculator and will reduce your redundant workload.

Technology Training

Microsoft Excel 2016 Nested ‘IF’ Formula

In our last video, we covered how to use “IF” formula in Microsoft Excel 2016. The IF formula allows you to ask a cell in a spreadsheet if a statement is true or false. This basic formula will allow you to build complex calculators in Excel which will help you be more efficient in your work.

In this video, we will dive deeper into Excel IF formula’s to show you how to ask if more than one statement is true or false. A good example of this would be calculating A, B, C or D grades for a class of students. In this example, you would need to print a letter grade for a score that each student received on a test. Here, a letter would represent a score between two numbers and the IF formula would need to check which statement was true and which was false.

This is a slightly more complex use of the IF formula, however, with a little practice, you will find that this formula can save you from a lot of data entry and additional work.

Technology Training

How To Use Microsoft Excel 2016 “IF” Formula

One of the most powerful functions of Microsoft Excel 2016 is the logical formula. Especially the “IF” formula. The IF formula is a way for you to apply logic to your spreadsheet cells to make customized calculations just by entering data. It takes a little practice but once you get used to this function you will be able to build complex calculators simply by using the IF formula.
IF formulas look to see if a statement is True or False. If the statement is true the logical formula produces one calculation. If the statement if False then it produces another calculation.
In this video, we will review a few of the operators that allow you to use the IF logic effectively. These operators include equal to, greater than, less than, greater than or equal to, less than or equal to and the not equal to. Using these operators, you will be able to manipulate the IF formula for most any calculation.
This may seem complex but once you understand the format used to build this formula it is really quite simple. It will help to start by understanding what you want the calculation to do. The best way to do that is to write out what you want to calculate. An example of this would be “IF the number is greater than or equal to 100, print yes. Otherwise, print no.” Setting this formula for any cell will allow Excel to apply logic and make a quick calculation.

CW Portal