Today() Formula in Excel
A common excel formula that can come in handy in many projects with Excel is the Today() Function. The Today() Function is used to return the serial number of the current date. By formatting the cell where the formula is used, it will return the Month, Day and Year of today. One of the easiest functions in Excel to use, here is the formula:
=Today()
Since today is June 5, 2007, the formula will return 6/5/07 since it is automatically formatted in date format. If you were to change the formatting to a number, the formula would return the serial number 39238 (If you are wondering what this number stands for, it is the number of days from January 1, 1900 – Tomorrow the serial number returned would be 39239)
There are many places in your projects you will be using the Today() function, from showing the date your spreadsheets were last updated to calculated how many days from today a deadline is. Once you learn to use this function regularly it will be a function you can’t live without.
Over the next few posts, I will be adding many example of how to use the numerous date and time functions built into excel. Please contact us with any questions or suggestions you may have.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
ISNA, ISERROR, and ISBLANK Formula in Excel
Excel has many built-in formulas that will check for errors for you. Were going to take a look at three of those formulas, which are ISNA, ISBLANK and ISERROR. Let’s start by taking a look at the formulas:
ISNA(value)
ISERROR(value)
ISBLANK(value)
Each of these formulas looks at value and evaluatues whether it is #N/A (with ISNA), #Value (with ISERROR), or blank (with ISBLANK).
When You Can Use These Formulas
These formulas come in very handy when you are working with a large group of formulas and need further analysis of the results. Having and #N/A or #Value in your results, as I’m sure you may have experienced, will most likely result in receiving more errors the more that particular result is analyzed. As you’ll see in the examples below, vlookup is a very common formula which will return an #N/A if the lookup value is not found. Or if you try to add a string to a number, the result will return #value.
Use the Record Macro Feature in Excel
One of the easiest ways to begin learning how to use the Macro ability of excel is to use the “Record Macro” button. To begin recording a macro, goto Tools->Macro->Record New Macro. After you hit that button, any actions you perform will be recorded. When you are finished performing the actions hit the “Stop” button on the box that popped up when you began recording. To view the code of the actions that was recorded, goto Tools->Macro->Macros and click on macro that was just recorded and hit edit.
The macro (or VBA) section of excel works great to help you perform actions in a spreadsheet that you perform repeating on a consistant basis. It will save you much time and effort (as well as headaches) and by looking at the code you can begin to learn how the code works.
The next step (which we will be adding soon) is to learn some basic functions to assist the macro, such as for loops and other application functions, to increase the ability of the macro to perform the feats you would like. The expandability of the macro is its greatest feature, as it can perform almost any process that you can think of.
I will be adding some examples in a near future post as to what the macros can be specifically told to do through recording them.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
COUNTA and COUNTBLANK Formulas in Excel
When using Excel, there will be many instances where you will want to count a particular range of cells. Excel has numerous ways built in already to count that range for you. One way we already explored is through the CountIf Formula, and also the Count Formula. Excel also features the CountA and CountBlank Formulas to make your counting even easier. Let’s go ahead and take a look at each of the formulas:
COUNTA(value1,value2,…)
COUNTBLANK(RANGE)
Both COUNTA and COUNTBLANK can accept individual objects or a Range of Cells. The difference between the two is what they are made to count. COUNTA counts the number of cells that are not empty and returns the number of cells containing some form of data. COUNTBLANK, on the other hand, will count the number of empty cells in the range and return the total (the opposite of COUNTA). Next let’s take a look at some examples.
Adding an Excel Macro Section to Excel Hints
I use excel macros everyday with my current job duties. A section I will soon be adding to ExcelHints.com is examples of macros that I use all the time. Macros can be used for several reasons, the most common is when there is a task that you need done on regular basis. Automating the process that you repeat all the time can speed up the amount of time you spend with the repetitious work.
Macros can also be useful to perform complicated tasks that would otherwise be very time consuming to perform. Here’s an example of how I used a macro today: we have a group of excel files with in numerous folders (about 2500 files in 750 folders) that needed an item changed within each file. If the macro found an instance of the item it was looking for, the item number was updated to the new number. After this macro was written, it took about 20 minutes for it to search the each file and make the appropriate changes. Doing this by hand would have taken several days with many people working on it.
I will also be adding a section about how I go about testing the macro before I use it on live data. Always remember to backup your original data before running any type of macro on it!
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
