Tag: microsoft excel 2016 formula

Microsoft Excel 2016 Date and Time EDATE functions

Post author By Jonathan Arnott
Post date June 23, 2017

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

Use Date and Time Functions in Microsoft Excel 2016

Post author By Jonathan Arnott
Post date June 16, 2017

In today’s video, we are going to show you how to work with dates and times in Microsoft Excel 2016. First, we will show you the logic that Excel uses to calculate dates, and then we will show you how you can work with this logic.

It is useful to understand that Excel sees dates as numbers, adding one number for every day after the first of January 1900. The hours, minutes and seconds are stored as decimal values. Knowing this will help you be able to work with the date and time functions more easily.

The next step in working with dates and times is to know the “Today” function, which will bring up the current date in any cell, and the “Now” function, which will bring up the current date and time. If you need to reference a date in your spreadsheet, it is easier to type in the equal sign followed by “Today” and Excel will automatically populate the date for you.

Another common function is knowing how to calculate someone’s age using their date of birth in a formula. To do this you will need to know how to use either the “INT” or Integer function or the “YEARFRAC” function. Both will calculate the difference between a recorded date and the current date simply by using them in an equation.

Working with dates and times in Excel is very straight forward once you know how they are being processed. Knowing how to use functions to manipulate dates and times will help make you more accurate and efficient when working in Excel.

Technology Training

How to use Relative and Absolute Cell References in Microsoft Excel 2016

Post author By Jonathan Arnott
Post date June 2, 2017

When working with a Microsoft Excel 2016 data table you may find that it is useful to define a constant outside of the data table rather than repeating it in every cell. Suppose you are trying to apply three different tax rates to a data table and you need to be able to adjust those tax rates without having to change them in every cell. Learning how to use cell references will allow you to do this by changing the reference rather than changing every row or column.

First, you will need to know the difference between defining a reference as relative or absolute. If for example, you are trying to add two columns together and you are using the SUM tool, you will take the first two number sets from each column and add the two together in a third column. This will automatically create an equation that you can extend to the rest of the numbers in the remaining column simply by placing your cursor over the sum of the first two numbers and dragging down the third column. This will extend the SUM equation to the rest of the numbers in the first two columns providing you the sum for the remaining number sets. This equation is using a relative constant because it changes with every set of numbers.

For the purpose of this video, we are going to focus on how to use an absolute constant. This is the equivalent of giving an equation an address to reference and whatever information you enter into that address will be reflected in the equation. In this case, the address is the X and Y access of the cell that you choose to use as your absolute cell reference. In our previous example, you would need to set up three cell references to correlate with the three tax rates. By labeling each tax rate A, B and C you can reference the tax rate in the data table allowing Excel to know which absolute constant to use in each equation.

Technology Training

Microsoft Excel 2016 Nested ‘IF’ Formula

Post author By Jonathan Arnott
Post date May 19, 2017

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.

Why More SMBs are Turning to the Cloud to Reduce TCO

Why SMBs Must Proactively Address the Threat of Mobile Hacks

Contact us

CW Portal