Okay, it's a day late, but here's an Excel Advent calendar, for you to use this month.
Yes, Excel can help you have a good TIME. It's also useful if you're looking for a DATE, or the perfect MATCH. (Insert your own bad puns here.)
Today we'll focus on the TIME function, because one of my clients recently wanted some help with that.
What Time Is It?
There are a couple of keyboards shortcuts for entering the date or time.
- To enter the current date on a worksheet, press the Ctrl key, and tap the ; key.
- To enter the current time, press Ctrl and Shift, and tap the ; key
If you want the date or time to update when you open the workbook, you can use a formula instead.
- To enter the date with a formula, type: =TODAY()
- To enter the date and time with a formula, type: =NOW()
Is It Too Late?
My client's workbook had a cell with a process start time. He wanted to check if the current time was later than the start time.
The formula in cell C6 compares C4 to C2. If the time in C4 is greater than the time in C2, the result is TRUE. Because C4 is empty in this screen shot, the result is FALSE.
If we use the NOW function in C4, it includes the date, as well as the current time. Even though the time in C4 is 8:17 AM, its value is much higher than the 9:00 AM time in C2.
I've added temporary formulas in column D, so you can see the numeric value for each cell in column C.
It's About Time
To calculate the current time value in C4, we can change the formula, to remove the date value.
The TRUNC function removes the fractional part of a number, so we'll subtract TRUNC(NOW()) from the NOW function.
Translated to English, the formula is: (DATE and TIME) minus DATE
The revised formula leaves the time value only in C4, and now the value in D4 is lower than the value in D2.
The formula in C6 now shows the correct result of FALSE.
More Date and Time Info
For the ultimate source of Excel Date and Time information, go to Chip Pearson's Dates and Times in Excel page. Chip explains:
- how Excel's date system works
- date and time arithmetic
- how to calculate working times
- formulas to find out when the next holiday occurs
- using dates and times in VBA
- and even the elusive DATEDIF function
On his blog, Chandoo, at Pointy Haired Dilbert, shows how to calculate Thanksgiving dates, although it might be a bit late to figure out when Thanksgiving is this year.
You have an Excel order form with a long list of products to select. After a customer enters quantities for the products they want to order, you want to print an invoice for the selected items.
Select the Products
Here's the OrderForm sheet, where quantities have been entered for four of the products, in column E.
Formulas in column B check the quantity column (E). If the quantity is filled in, then the next available number is calculated.
Print the Order
On the Invoice sheet, formulas pull the data from the Order form. For example, this formula shows the name of the first product that was ordered:
Here's the Order Form sheet, ready to be printed. The numbered rows from the Products sheet have been added to the Order form. Products without a quantity, such as Sweater, don't appear.
The Sample File
To download the sample file, please visit the Excel Sample Files page, on my Contextures website. In the Functions section, look for FN0027 - Print Marked Invoice Items.
In a lookup table, how can you get Excel to find the item that's the exact match, including the upper and lower case?
For example, in this table, row 4 is M7 and row 5 is m7. There's a different value for each of these in column B. If I do a lookup for m7, I want the result to be 5, not 4.
VLOOKUP Is Not Case Sensitive
You could try a VLOOKUP formula, to find the value for m7, but it's not case sensitive. In the following screenshot you can see a VLOOKUP formula in the formula bar, and the result of 4 in cell E1.
On the Microsoft website, there's an article that explains how to perform a case sensitive lookup. One sample formula uses IF and EXACT with VLOOKUP to check the case. In our sample sheet, the suggested formula is:
=IF(EXACT(D1,VLOOKUP(D1,A1:B6,1,FALSE))=TRUE,VLOOKUP(D1,A1:B6,2,FALSE),"No exact match")
However, this doesn't work in our sample table, because it stops at the M7, and that's not an exact match for the lookup value m7.
Case Sensitive INDEX MATCH
The Microsoft article has other sample formulas, including an INDEX MATCH, but they all have the same problem, stopping at the M7 above the m7 value.
Fortunately, a search in Google Groups led me to an array formula posted by my old friend, former Excel MVP Peo Sjoblom. For our table, Peo's formula would be:
This is an array formula, so type the formula then press Ctrl+Shift+Enter. Curly brackets will automatically appear at the start and end of the formula.
In the screenshot below you can see the formula, and the correct result of 5, in cell E1. The formula finds an exact, case sensitive match for the lookup value.
More Excel Function Examples
It’s Friday, and things are slow at the office. To liven things up, you could create bingo cards in Excel, and organize a game during the lunch hour.
In this example, there are three cards, each with a set of random numbers. You’ll need one of those numbered ball popper machines though, or create a number selector in Excel.
Continue reading "Create Bingo Cards in Excel"
Word 2007 has a new Building Blocks feature that lets you quickly add items, such as cover pages, text boxes, watermarks and page numbers. If you're writing a scientific document, you can also insert equations.
Insert an Equation
- On the Ribbon, click the Insert tab.
- In the Symbols group, click on Equation, then click the equation that you want to insert.
Save an Equation
After you insert an equation, you can modify it, then save it to use again later.
- Click on the equation in the Word document, to activate it.
- Click the arrow at the bottom right of the equation box, and click on Save as New Equation.
- Enter the details for your equation, then click OK.
- When you close Word, you'll be prompted to save the changes to the Building Blocks template. Click Yes to save your changes.
Insert a Saved Equation
Saved equations are listed in the Building Blocks Organizer. Open it and select the item you want to insert.
- On the Ribbon, click the Insert tab.
- In the Text group, click on Quick Parts, then click Building Blocks Organizer
- In the Building Block Organizer dialog box, click on the Gallery heading, to sort the list by type.
- Scroll down to the Equations, and click on an equation to select it.
- Click the Insert button.
PUP Add-in With Free Source Code
In other news, John Walkenbach is having a rare clearance sale. If you buy a licence for his Excel PUP add-in, for Excel 2007 or earlier version, he'll throw in the source code free. It's a great way to look under the hood, and learn some new tricks in Excel VBA. And the PUP add-in is packed with features that will help you use Excel more efficiently. It even has a few games, for those times when you're tired of working so hard.
The offer is only good until November 17th, so if you're thinking about buying it, don't think too long.