Sign Up to View Tutorials
We have begun uploading Microsoft Access tutorials to teachmsoffice.com. You can view all of our current MS Access tutorials here: Microsoft Access Tutorials
We will be uploading many more Access tutorials that will help everyone from the beginner to the advanced users with Access. Check back often for more tutorials and updates!
Here, we are going to cover dates in Excel. This post is the last of a three-part post that specifically covers how to enter dates into Excel, how Excel sees and stores dates, and some of the more difficult date calculations, such as adding and subtracting dates in Excel.
P3 – Add & Subtract Dates in Excel
Adding and subtracting dates in Excel can be very easy or very complicated depending on exactly what you want to do. This article will explain how to perform basic calculations with dates and also cover why some calculations are seemingly complex.
The most basic date calculation is to add or subtract a day from a date. This is achieved quite simply by adding or subtracting an integer from a date within a cell in Excel. This particular calculation is so easy to perform due to the fact that Excel stores dates as sequential serial numbers, whereby the next day is simply one higher than the previous day (How Excel Views, Stores, and Manages Dates). Therefore, to add or subtract a day from a date, you merely perform basic math within Excel.
The tricky part comes when you have to add or subtract months and years. Now, this is not necessarily complicated, there are just more steps involved compared to adding or subtracting a day from a date. There are more steps involved in this process because of the fact that not all months have the same amount of days and once every four years there is a leap year, which means that February has 29 days instead of 28 days and the year has 366 days instead of 365 days. As a result, we have to use a combination of functions in Excel in order to perform month and year date calculations.
There are many date functions in Excel, and to perform month and year calculations, you usually need to nest multiple functions within each other. Therefore, you need to know how to nest functions in Excel and you need to learn how to use Date and Time Functions in Excel.
Once you have mastered date calculations in Excel, just about everything else will seem easy. If you want to learn more about date calculations and time calculations in Excel, check out these Date and Time Tutorials for Excel.
Posted in Dates in Excel
|
Tagged add, calculations, dates, day, excel, functions, month, p3, subtract, tutorials, year
|
Here, we are going to cover dates in Excel. This post is the second of a three-part post that specifically covers how to enter dates into Excel, how Excel sees and stores dates, and some of the more difficult date calculations, such as adding and subtracting dates in Excel.
P2 – How Excel Views, Stores, and Manages Dates
Now, let’s talk about how Excel sees or stores dates. Excel stores dates as serial numbers. To see this, just enter any date into an Excel spreadsheet and then change the format of the cell from a date format to the “General” format. For instance, if you enter the date February 2, 2011 into a cell, that is the date that we will see; however, Excel interprets this date as the integer 40576.
Excel stores dates as serial numbers so that we can perform calculations with these dates. The serial numbers are in sequential order, and that means that each successive day has a serial number that is one higher than the day before it. For example, the serial number 1 corresponds to the date January 1, 1900. The date mentioned above, February 2, 2011, has a serial number of 40576, which means that this date occurs 40,576 days after January 1, 1900.
Storing dates as serial numbers makes it easy for Excel to perform date calculations because the program simply has to add or remove integers (whole numbers) from a date to perform a calculation. However, this can make it a bit more confusing for us humans who do not look at dates as serial numbers.
One common problem that people run into with dates and serial numbers is when an entire spreadsheet has had its formatting changed to “General.” This is usually OK for text and basic numbers, but if you also have a lot of dates on the worksheet, this can pose a problem. If you are not aware that Excel stores dates as serial numbers, you will naturally think that all numbers in this spreadsheet are in fact just numbers. That will cause the user to mess-up the spreadsheet because they will then apply number formatting to dates. In this situation, the user will have to go over each number and determine whether it is a date or not, and, if it is a date, apply date formatting to that specific cell instead of number formatting. This is an annoying situation, but it happens more often than you might think, especially when people are sending worksheets back and forth between multiple users.
Now that you know how Excel views and stores dates, you are probably interested in performing some type of date calculation, function, or manipulation. We have many Date and Time Tutorials for Excel and you should check them out if you want to learn more.
The next post in this series deals with the basics of date calculations.
Here, we are going to cover dates in Excel. This post is the first of a three-part post that specifically covers how to enter dates into Excel, how Excel sees and stores dates, and some of the more difficult date calculations, such as adding and subtracting dates in Excel.
P1 – How to Enter Dates into Excel
First, lets talk about how you can enter a date into Excel. Thankfully, Excel makes it very easy for us to enter a date into a spreadsheet. For the most part, you can type a date into any cell in Excel just the way that you would write it. Once you type the date and hit the enter key, Excel automatically knows that you have entered a date and applies a pre-formatted date format to the cell containing the date. Now, this does not always work and there are differences with how a date is displayed across different countries and languages, but, below, there are some examples of how you can type a date into a cell in order for Excel to understand that you are entering a date – note that there are many different ways to display the date after it has been entered into the cell, but that deals with formatting.
- February 14, 2011
- Feb 14, 2011
- 2/14/2011
- 14-Feb-11
The main idea here is that if you enter something that looks like a date to a person, Excel will usually see this as a date and convert it into a date format. Now, you may be wondering why it is important for Excel to see a date as a date so long as the person looking at the date understands the date. It is important because Excel can only perform calculations on dates that it knows are dates. For instance, if you try to add or subtract a day from a date that Excel doesn’t know is a date, you will get an error. If Excel knows that you are working with a date, it will complete the calculation. This is why it is so important to make sure Excel knows that your dates are in fact dates. The easiest way to confirm this is to see that the cell format has changed from “General” to “Date” or something similar after you entered the date into the cell.
That’s pretty much all there is to the basics of entering a date into an Excel spreadsheet. If you want more information on working with dates in Excel, check out our Date and Time Tutorials for Excel.
The next post in this series deals with how Excel sees and stores dates.
This post is designed to introduce you to the world of the UserForm in Excel. Now, a UserForm is nothing more than a customizable way to allow users to enter, view, and retrieve data from Excel. Think of a UserForm as a pop-up window that you can design and control, because that’s basically all that it is.
A UserForm is a pop-up window that you create in order to help a user do something within Excel. Essentially, this is used as a front-end system where the actual spreadsheet is being used just to store data. The user interacts with the UserForm, but the data used in the UserForm is actually going to be stored within Excel. This all sounds nice, but it can be confusing until you actually see an example; therefore, I’ve included an example of a UserForm and why someone would use it below.
Scenario:
You are trying to keep track of employee or customer information using an Excel spreadsheet. You have a specific worksheet that contains all of the information about the employees or customers and you do not want anyone to mess-up this data. Also, you want to create a simple and easy-to-use solution for entering and retrieving data from the spreadsheet.
Solution:
Create a UserForm that asks for all required input about the employee or customer and then have the UserForum enter this data correctly into the spreadsheet. And, to retrieve data, create another UserForm that asks for something like a unique employee or customer ID and, when supplied with this ID, the UserForm displays the information for the corresponding employee or customer.
Here is a screen-shot of a very simple version of this UserForm (this can be as simple or complex as needed):
This UserForm asks for the Name and the Position of a User. Once this information is entered and the “Add User” button is clicked, this information will then be automatically entered into the correct place within the correct Excel spreadsheet.
Notice that the UserForm, pictured above, is Continue reading →
In this post, we are going to talk about adding logic to your macros and VBA for Excel. We are going to talk about how to have your code make decisions. Essentially, this just means that we are going to go over IF THEN Statements for macros and VBA.
Adding logic to your macros and VBA allows you to make more complex code that can do different things based upon varying input or changing conditions. This allows you to create decision structures that allow for a more robust and versatile macro. The most widely used example of logic within VBA and macros is the IF statement.
The IF statement allows you to create very simple yes or no (true or false) checks in your code. IF something evaluates to true, do this, otherwise, do nothing or do something else. For instance, IF the value in cell A1 is 10, run the macro, otherwise don’t run the macro. This is very simple logic that allows you to create macros that can, essentially, adapt to the current situation. Now, the macros can’t actually adapt, but their execution can change based on the current environment or input so long as the person writing the macro accounted for these potential changes.
There are a number of different versions of the IF statement, but these are really just iterations of the basic IF statement. Below is a listing of different IF statement types with links to tutorials on each type.
- IF THEN
- IF THEN ELSE
- IF THEN ELSEIF
- IF THEN ELSEIF ELSE
- One Line IF THEN Statement
- IIF Function (IF statement in function form within VBA)
To learn more about adding logic to VBA and macros and the IF THEN statement, check out an extensive listing of Excel VBA and Macro Tutorials.
We are updating the tutorials with a lot more of everything in the coming weeks. This includes more tutorials for Microsoft Word, PowerPoint, and even Access (more about that later).
Check back soon for updates!
Pricing bonds in Excel is something that can save you a lot of time and is fairly easy to do once you understand the functions involved. The thing to remember about all bond pricing is that you are basically just dealing with the concept of the Time Value of Money (TVM). The time value of money just means that money today is not worth the same amount tomorrow. With bond pricing, you are really just trying to see what the coupon payments combined with the par value are worth today; that will give you the present value or the supposed purchase price of the bond.
The part of bond pricing that can throw people off a bit is that, when you are talking about basic bond price and present value calculations, you are taking into account the compounding of interest. These calculations are almost always done with the assumption that you will reinvest all coupon payments back into a bond or another investment that pays exactly the same coupon rate as the initial bond that paid you the coupon in the first place. Thus, the interest or coupon payment is being compounded throughout the life of the bond. Note that all present value and future value or yield to maturity calculations will make this assumption. Though, it is a bit odd when you think about because you are not going to be able to purchase new bonds with the same coupon rate and yield to maturity immediately after you receive coupon payments.
It is safe to say that the reason these calculations take into account compounding is simply due to the fact that compounding, generally speaking, will have a small effect on the total present value, future value, or yield to maturity of the bond. Also, it is simply easier to make these types of calculations with the assumption of compounding since the functions in Excel all make this assumption, as do many financial calculators.
If you want to learn more about bonds and bond pricing in Excel check out some finance in Excel tutorials and training.
.
Posted in Finance in Excel
|
Tagged bonds, compounding, coupon, excel, excel finance, finance, interest, present value, pricing, training, tutorials
|
In this post, I’m going to talk about some of the reasons that macros and VBA are really just so cool, especially for Excel.
You can create macros for most, if not all, of the Microsoft Office programs but, by far, the people who use macros and VBA the most are those who use Excel. The reason for this is that Excel’s real purpose is for data analysis and there are about a million different things that you can do to analyze data in Excel. As such, there are many features that you can create with a macro that are not included in the program itself. Simply put, macros in Excel save you time, money, and effort.
A macro in Excel can be used to ‘clean’ or edit a list of 50,000 emails in seconds or it can be used to scan an entire directory of Excel workbooks in order to find the necessary data. So, from here down in this post, I am going to give you some scenarios where macros and VBA in Excel can help save you time, money, and effort.
Scenario 1:
The owner of a store uses POS (point of sale) systems to record daily sails, which include the amount of each purchase and the items in each purchase. Each POS system exports a csv or formatted text file with the thousands of transactions. The problem is that the owner of the store has to import these files by hand into Excel, make sure the data is correctly imported, consolidate the data, and update another spreadsheet to represent the new inventory levels. This process takes probably 1-2 hours each day.
Solution:
Build an inventory management system using Excel. The backbone of this system is going to be a series of macros that will do all of the heavy lifting for you. This means that the owner now only has to open up one spreadsheet, hit a button, select the day’s csv files which were exported from the POS systems, and hit enter. The macros in the Excel workbook will import and arrange all of the data as well as consolidate the data and update a separate Excel workbook in order to reflect the new inventory levels. As well, the macro can alert you if inventory levels are low for certain products and can even send email invoices to a manufacturer in order to have more product delivered. Remember, that all of this can be done with the click of a button if the owner uses macros and VBA in Excel. Without a system of macros, this will take the owner hours to complete.
Scenario 2:
A user regularly imports thousands of emails from a subscribers list on their website. The user then attempts to remove all emails from the list if they appear to be spam related or simply fake. In order to do this by hand, the user must continually search for suspicious emails and delete them as they appear. Doing this by hand could take hours, especially with really large lists, say over 1,000, 10,000, 100,000 emails etc.
Solution:
Create a macro that will search through the list of emails for the user and remove any suspicious looking emails. The macro could achieve this any number of ways. Perhaps the easiest way to achieve this is to retain a separate column in the workbook that contains all of the phrases or words or domains that, if contained in an email address, should cause the email address to be deleted. The macro can then search the list of emails and remove all that contain the keywords located in a specific column. The macro can do this in a matter of seconds.
The point of this article is really just to show you that macros and VBA, especially in Excel, can really save you a lot of time. This is especially true when you use a macro to automate an often repeated task. Basically, if you want to save yourself time when working in Excel, you will probably need to get some macros to help you out.
If you need any custom macros you can always contact the Excel gurus here.
All this week, we will be adding new Excel training tutorials for finance. These video tutorials will cover topics ranging from the Time Value of Money (TVM) to Capital Budgeting and Portfolio Analysis. All of the video tutorials are high definition and will include downloadable files so that you can follow along with the tutorial.
Finance training in Excel is of paramount importance if you plan to work in the field of business, accounting, finance, etc. In reality, it does not matter how well you can use a financial calculator if you cannot do financial calculations in Excel using the built-in financial functions or by creating your own functions. This is because all business and finance professionals spend all day working in Excel in order to generate reports, analysis, and valuation calculations. Excel is the crutch of the industry and this is why learning finance in Excel is simply a must for all business, accounting, and finance related professionals.
In accordance with this, we will be creating many new finance in Excel video training tutorials.
View our Training Tutorials. Sign Up
.
|
|
