I like to use Notepad to make notes as I work. In July, I described how I type .LOG at the top of the Notepad file, so the date and time are automatically entered when the file opens.
That’s a handy feature, but I wanted to timestamp the files as I was working to, to record my start and stop times. There are date and time shortcuts in Excel and Access, but unfortunately those shortcuts don’t work in Notepad.
I obviously hadn’t looked too hard, because today I found the shortcut that I’ve been looking for — listed right there on the Edit menu in Notepad.
Now, if I want to insert the date and time, I press the F5 key, and it’s automatically entered for me.
Have you switched from Excel 2003, with its compact toolbars, to Excel with its Ribbons commands?
If you’ve switched, did you find it easy to adjust to the new User Interface?
Classic Menu Add-in
I’ve heard lots of complaints about switching from those toolbars to the Excel Ribbon.
So, when I noticed an Excel add-in called, “Excel 2007 Ribbon to old Excel Classic Menu Toolbar” recently, I decided to download the trial version and take a look.
Transition to Excel Ribbon
Maybe this add-in would help people make an easier transition to Excel 2007. The add-in was at an introductory sale price of $8.99, so it wouldn’t be too big an investment.
The download went smoothly, and the installation took a bit longer than I expected, but nothing too serious.
Annoying Pop-up Window
However, immediately after the installation, a pop-up window appeared, asking me to send my name and email address, and subscribe to a newsletter.
I didn’t want the newsletter, and tried to close the window, but there’s no way to get rid of it.
The X button has no effect, and the Send button demands that you fill in the boxes.
Finally, I was able to close it with the Task Manager.
Uninstalling the Add-In
Because of that bad experience, I haven’t even tried the product and I’ve decided that I don’t want anything to do with this company.
I’ll be uninstalling the trial version in a couple of minutes.
By the way, the trial version only installs two of the old style menus (File and Edit).
If you want the rest of the menus, you’ll have to buy the full version.
I won’t be buying it, but maybe it’s something you’d like to try.
Loved Those Tiny Toolbars
This Excel Toolbar add-in did make me realize one thing though.
It’s not the drop-down menus I miss, it’s the space-saving toolbars, with their tiny rows of efficient little buttons.
[Update – John Walkenbach has sold his website and this product is no longer available]
About a week ago, John Walkenbach had a clearance sale for his PUP Add-In for Excel. PUP stands for Power Utility Pak, and that’s not an overstatement.
I’ve been using the latest version, PUPv7 for Excel 2007, for the past week, and am amazed by all its features.
When installed, PUPv7 adds a tab to the Excel Ribbon, with six groups of commands. The Ribbon tab’s groups don’t collapse if you make the Excel window narrower, like the built-in tabs do, but that’s a minor quibble.
There are too many features to list, but here are some of my favourites, so far.
PUP Bookmarks
Create a bookmark to mark a range in any Excel file, and you can quickly return to that file and location from the PUP Ribbon tab.
Create a bookmark
Create Workbook Contents Sheet
There’s a list of workbook tools, including the handy Create Workbook Contents Sheet command. This creates a cover sheet in your workbook, with a set of hyperlinks or buttons that link to the other sheets in the workbook.
Maybe the next version will add a hyperlink back to the contents sheet on each indexed sheet, in a specified cell.
Create Workbook Contents Sheet command
Customize a New Workbook
With the Customize a New Workbook command you can quickly create a workbook with a sheet for each month, each weekday, a numbered series or a list of items.
Customize a New Workbook
Text Tools
In the Modify Cells and Ranges drop down is a Text Tools command. With it, you can change the case of text in selected cells, add text within existing text, or remove text or spaces.
With this tool you won’t have to create formulas or macros when you want to modify text, just fill in boxes and click Apply.
Many More Features
Those are just a few of the features in PUPv7.
There are also worksheet functions, randomizer tools, workbook reports and many more features.
I highly recommend that you download the free trial (30 days) or purchase a copy ($40 US).
PUPv7 is a real time saver as as you work in Excel 2007, and it should pay for itself very quickly. PUPv6 is available for Excel 2003 or earlier versions.
There are sites that offer Christmas Planner templates, usually in pdf format, which you can download and print.
Then, store the printed sheets in a binder, and write out your task list, holiday budget, gift list and calendar.
It’s Better in Excel
I’m sure that helps people who are trying to organize a hectic holiday season, but Excel would be a better tool for all those lists and budgets.
So, with my daughter’s help, I created an Excel Christmas Planner that you can download from my web site.
Instead of hand writing your lists, and doing your budgeting on a calculator, enter all the details in an Excel workbook, and let it do the heavy lifting for you.
Planning Sheets
There are 15 sheets in the planner, currently, and we’ll add more, if we have new ideas.
For example, there’s an extended weekly calendar, so you can see what’s happening on specific days.
You can also make a list of upcoming tasks, keep a master gift list, keep costs in line with a holiday budget planner, and many more sheets.
Dinner Planner
The workbook includes a dinner planner, which will help you schedule the preparation for any large meal.
The dinner planner is helpful for smaller meals too, if they have lots of preparation steps!
You can get organized for the American Thanksgiving this week, or Christmas next month, or a weekend dinner party, any time of the year.
What Should We Add?
It’s our first draft of our Holiday Planner, so there’s probably much more that we could add. If you have any suggestions for improving it, we’d love to hear from you!
Unlike Word, excel doesn’t have a menu command that will insert the last saved date.
However, you can use a bit of programming to insert the date, assuming the workbook has been saved. There is sample code in the next section.
Note: You could also create a User Defined Function (UDF) to calculate the date. However, I find that UDFs can slow down a workbook, so avoid them, if possible.
Macro VBA Code – Last Saved Date
Here is the code that inserts the workbook’s last saved date. It’s entered on a sheet named Data Entry, in cell A1.
Tip: You could add an event procedure in your workbook, so this code runs automatically, any time the workbook is saved.
Sub GetLastSavedDate()
On Error Resume Next
Dim sSaveDate As String
sSaveDate = FileDateTime(ActiveWorkbook.FullName)
If sSaveDate = “” Then
MsgBox “Could not determine save date.”
Else
Worksheets(“DataEntry”).Range(“A1”).Value _
= “Last Saved: ” & sSaveDate
End If
End Sub
This week I’ve been working on date formulas, from very simple ones, to complex formulas that calculate workdays per month, based on start and end dates that can span several months.
Extract Information from a Date
Many times I need to pull a bit of information from a date, such as the year, month or weekday.
In the section below, I’ve listed the sample Excel formulas I would use, to calculate specific dates in Excel.
For all formulas, the date — December 29, 2008 — is in cell A2.
Date Calculation Formulas
Date Calculation Formulas
Here are the formulas to extract information from a date in cell A2.
To Calculate
The Formula
The Result
Year
=YEAR(A2)
2008
Month Number
=MONTH(A2)
12
Month Name (short)
=TEXT(A2,”mmm”)
Dec
Month Name (long)
=TEXT(A2,”mmmm”)
December
Day of the month
=DAY(A2)
29
Weekday Number
=WEEKDAY(A2,1)
2
Weekday Name (short)
=TEXT(A2,”ddd”)
Mon
Weekday Name (long)
=TEXT(A2,”dddd”)
Monday
Year Month
=TEXT(A2,”yyyy mm”)
2008 12
Using Calculated Dates in Pivot Table
If I plan to create a pivot table from data that contains a date field, I usually calculate the year and month in the source data.
Then I can add those fields to the pivot table, instead of the individual dates.
Yes, the pivot table could automatically group the individual dates by year and month, but that can limit other functions in the pivot table.
For example:
if two pivot tables are based on the same data, grouping one pivot table by month would cause the other pivot table to also be grouped by month.
if a field is grouped, you cannot add calculated items to the pivot table
pivot table error message – cannot add a calculated item
Video: Pivot Table Grouping Tips
This video shows how to group pivot table dates by month and years, and how to group text items manually.
There are examples for grouping dates, number and text fields. You’ll also see solutions for fixing pivot table grouping problems, such as the error message, “Cannot group that selection”
Occasionally a client asks me to create a shared workbook in Excel, so two or more employees can work in it at the same time. It sounds good in theory, but I always try to come up with a different solution.
There are many reasons for avoiding shared workbooks in Excel.
David’s interests have shifted over the past couple of years, and he’s now documenting Firefox features. You can find his Firefox Lessons in a new section on his web site.
Meeting for Lunch
Last week David was visiting relatives in Canada and we met for lunch on Friday. We had a great chat, and I learned quite a bit about his very interesting work history.
I had previously met David at a Microsoft MVP Summit in 2001. That was the first year I received the award, and David had been an Excel MVP since 1999.
It was good to see him again, and I hope he enjoys the rest of his visit.
Excel expert, and former Microsoft MVP, David McRitchie
