Date and Time Entry

Month Day, Day Month

QDE An Excel Date Entry Add-In
Ron de Bruin

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic



See all Topics

Week numbers

Who's counting?

For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years.

2004 had 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.

Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.

Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.

Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

In Access:
DatePart Function

If your work week is always Saturday through Friday then
datepart("ww",[DateField],7,1)

will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.

The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.



See all Topics

Default Save

Choose your own location

When you choose to save most Office files, the Save dialog box defaults to the Documents or My Documents folder.

(The following directions work in 2007+, but you need to click on the Office button in the upper left corner of the Window)

Word

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Excel

Tools>Options. On the "General" tab change the default location.

PowerPoint

uses Tools>Options and the "Save" tab.

Access

Tools>Options and the "General" tab for Databases and Projects

Publisher

Tools>Options "General".

Outlook

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web

appears to require the same sort of spelunking.

Change the folder where e-mail messages and attachments are saved


If you don't want to change the default, but would like to be able to quickly go to an alternate site, open the Save or Save Attachment dialog box. On the left side of the box is the Places Navigation bar. If you click the Desktop icon, that location will be used to save the file.

You can add spots to the bar. Browse to the specific folder. Highlight the folder and click the down arrow beside the Tools option. Select "Add to My Places."

The file or e-mail attachment can then be saved where you want.



See all Topics

Accustom Yourself to Excel

Shake hands with a worksheet

How to get used to the new user interface in Excel 20013.

• Add a Get Started tab to the Ribbon
  
  
• Take advantage of the Quick Access Toolbar
  
  
• Minimize the Ribbon, maximize your workspace
  
  
• Choose your own default font
  
  
• Restore missing worksheet tabs
  
  
• Freeze panes here, there, and everywhere

Surviving the switch to Excel 20013



See all Topics

Camera Tool

Smile!

(This is the pre-2007 routine. For 2007+ see the end. )

To create a linked picture of part of a spreadsheet for use elsewhere:

1. Edit>Copy the Cell or Range
   
2. Choose the target cell
   
3. Hold down the Shift key and choose Edit>Paste Picture Link

If you're going to do this frequently, go to Tools>Customize. Choose the Commands tab and locate Camera in the Tools category. Drag to place the Camera button on your toolbar.



If the Camera tool is on your toolbar:

1. Select the Cell/Range
   
2. Click the Camera tool
   
3. Move the cursor to the target position and click to insert the linked picture

To create a static picture from cells, or a chart to use in another part of your workbook, or another program:

1. Clear cell gridlines if you do not want them displayed in your picture.
   (Click Options on the Tools menu, click the View tab, and then clear the Gridlines check box")
   
   
2. On the worksheet or chart sheet, select the cells or click the chart you want to copy as a picture
   
3. Hold down SHIFT and click Copy Picture on the Edit menu
   (For the best picture quality, make sure As shown on screen and Picture are selected)
   
4. Click OK
   
5. Select the worksheet or other document where you want to paste the picture
   
6. Edit>Paste.

You can use the Picture toolbar to change the image. (to display the toolbar, right-click any toolbar and choose Picture) To paste information you've copied from another program as a picture in Microsoft Excel, hold down SHIFT and click Paste Picture or Paste Picture Link on the Edit menu. You can, also, create a dynamic linked text box by using the tool on the Drawing toolbar to place the object on the worksheet.
Now click to the Formula box. Type = and the click on the cell you wish to link.

Also see:
Copy - Paste Methodology
by Jon Peltier
 
The process is a little different in 2007+. Either add the Camera tool to the Quick access toolbar, or just select the range and drop down the Paste options. Choose copy as Picture. Next pick a location and  choose Paste as picture.



See all Topics

OLAP Cubes

More dimensions than Star trek

When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)



Wikipedia:
OLAP



See all Topics

VBA Named Arguments

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

When you work the MsgBox function this way, the order of the arguments can't be changed.

Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as:

MsgBox "Hello World!", , "My Message Box"

Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value.

For instance, the previous statement can be rewritten as:

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"

(To find out a function's named arguments, select the function in your code and press [F1].)



See all Topics

Zero 0

Zero is nothing

If a zero isn't worth anything, why show it?

Here is a Microsoft tutorial about how to deal with zilch:

• Display or hide all zero values on a worksheet
  
  
• Use a number format to hide zero values in selected cells
  
  
• Use a conditional format to hide zero values returned by a formula
  
  
• Use a formula to display zeros as a blanks or dashes
  
  
• Hide zero values in a PivotTable report

Hide Zeros



See all Topics

What's New in Excel 2013

Different can be good

Microsoft has a information that explains the highlights of the new features of Excel:

• Improved ribbon
  
  
• Access workbooks in new ways
  
  
• Make fast, effective comparisons from lists of data
  
  
• Sparklines
  
  
• Create workbooks with more visual impact
  
  
• Collaborate on workbooks in new ways

There are also other links.

What's New in Excel 2013



See all Topics

Split the Costs

Split the sheets (?)

Joe Chirilov presents a spreadsheet solution to a friendship breaker.

Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. How do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?

Split Costs

You can download a spreadsheet or watch a video here:
Split_Costs.zip



See all Topics

Match Format Paste

Copy/Paste formatting in Word, PowerPoint or Excel

When you copy information from a Web page or another document, the formatting will also be copied.

To match the formatting of the target document, copy the text and place the cursor where you want to insert the copy.

Then, go to Edit>Paste Special, and select the Unformatted Text option.
(Click the arrow under Paste in the Clipboard group on the Home tab in 2007+)

The clipboard text will be pasted to match the target.

Another way when using Word 2002 + is to click on the "Smart icon" that appears at
the lower right corner of the pasted text. You can then choose to keep the original formatting, match the destination formatting, keep text only, or apply a new style.

An additional way to transfer just the formatting between documents is to highlight the text with the formatting you wish to copy and then hold down the Ctrl key and the Shift key and press the C key (Ctrl+Shift+C). Release the keys. Select the text you want to have formatted. Hold down the Ctrl key and the Shift key and press the V key (Ctrl+Shift+V). Only the formatting is copied, not the text.
In Excel use Edit>Paste Special and select the "Formats" option.


What's So Special About "Paste Special"? Video

Paste Special can also be used with graphics.

You can change Word's default behavior; choose whether to paste Inline or Floating.

Microsoft Word MVPS FAQ



See all Topics

Spreadsheet Bloat

Formatting bloat

Avoid applying formatting to more than just the active area of your worksheet. Extraneous formatting will confuse Excel about the last cell in the spreadsheet.

Depending on your OS and specific configuration, you could see symptoms ranging from Excel not responding to various error messages concerning page faults, low virtual memory, and access violations. You may see a warning message; "Too many different formats."

To resolve this issue, make sure you select only the particular range of cells you want formatted when you apply specific formatting or select Format>AutoFormat.

You can use Ctrl+End to see where Excel thinks the last cell is.
For more information, check out:

How to reset the last cell in Excel

Also see:

Spreadsheet Diet

Microsoft:
Formatting Cleaner Add-in



See all Topics

More than Sudoku

Game with Excel

"IT may be the biggest tool in the Office, but Microsoft Excel can be cool too"

Here's an article on off-prescription Excel.

Play with Excel

Here are some of the games you can play for free:

• Excel fun
  
  
• 3D Viewer
  
  
• Battleship
  
  
• Blackjack
  
  
• Breakout
  
  
• Golf Stats
  
  
• Mastermind
  
  
• Maze
  
  
• Minesweeper
  
  
• Reversi
  
  
• Rubix
  
  
• Slots
  
  
• Sudoku Solver
  
  
• Tetris
  
  
• Video Poker

ExcelTrick.com  

Mashable.com



See all Topics

Date Arithmetic

The drunken cousin

Working with dates has a few twists.

Excel believes that time began on January 1, 1900.

Each day since then is counted so that September 1, 2003 in Excel-speak would be → 37,865.
9/1/03 7:33 A.M. is a decimal → 37865.31458333333

When you subtract one date from another, for instance 9/1/2003 (A1)minus 7/4/2001 (A2),
Excel displays the odd answer of → 2/27/1902.

Excel formats the result of a formula with the same format as the source cells,
Right-click the formula cell (=A1-A2).
Select Format Cells ..., and then choose a Number format with zero decimals.

The correct number of days → 789 will now be displayed.

Another way is to use the rarely documented DATEDIF function. Chip Pearson calls it "the drunken cousin of the Function family."

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

Here's THE source for date math:
Chip Pearson:
All About Dates
Also:

John Walenbach:
Extended Date Functions Add-In

"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."

MS Knowledge Base:
How To Use Dates and Times in Excel




See all Topics

Reset Shortcut Menu

Context menu redo

For some reason (to be determined later) my shortcut menu started to accumulate a string of "Edit Formula" and "Delete Formula" entries.

They didn't cause a problem, but were distracting.

This little VBA code puts it back to the original settings until I can find a cause.

Sub ResetShortcutMenu()
Application.CommandBars("Cell").Reset
End Sub

Macros that Customize and Control Shortcut Menus



See all Topics

Office VBA Tricks

Video + Free code

"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."

• Update Word Document Statistics in the Title Bar
  
• Create Outlook Rules Programmatically
  
• Delete Repeated Text Throughout a Word Document
  
• Run Macros Based on the Value of One or More Excel Spreadsheet Cells
  
• Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
  
• Display Reminder Information When a User Opens an Office Document
  
• Synchronize an Access Main Form to a Subform and Vice Versa
  
• Log Worksheet Changes to an XML File
  
• Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
  
• Use the Office Assistant as an Alternative to Displaying and Retrieving User Input

Ten Tips for Office VBA Developers

VBA Tips & Tricks

Getting Started with VBA in Office 2010

Download Office 2013 VBA Documentation


(VBA is VBA and is, in most cases, usable in all versions of Office)



See all Topics

Chiropractics for Excel

Knead and pound numbers

Chad Rothschiller, a program manager on the Excel team, discusses using formulas to 'clean up' data in Excel.

Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.

This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.

I'm sure you've been faced with at least one of theses problems:

• Import the data and don't accidentally drop the leading zeros!
  
  
• Formatting SSN
  
  
• Inserting hyphens
  
  
• Fixing up names
  
  
• Lower case E-Mail Address
  
  
• Format Home Phone
  
  
• Inserting parenthesis & hyphens
  
  
• Trim extra spaces from Address
  
  
• Add City values to the new table
  
  
• Make all State value upper case
  
  
• Fill in Postal Code
  
  
• Finalize the values

Manipulate and massage  



See all Topics

Spreadsheet Boo-Boos

Design suggestions

A few links to sites with advice on spreadsheet design and error detection.

European Spreadsheet Risks Interest Group:
Spreadsheet mistakes - news stories

Raymond R. Panko, PhD University of Hawaii

What We Know About Spreadsheet Errors



See all Topics

Web Data

Numbers from the ether

Excel has had a feature called web query. Here's an add-in that makes it a little easier.

"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel.

The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option. The system extracts data by learning from a user’s selection of data they wish to capture into Excel. The more selections, the more the system is trained."

Web Data

Getting data from the Web in Excel

2013 Web Data YouTube



See all Topics

All the Basics

All(most) all you need to know

Office.Microsoft.com has a short demo that shows you the main things anyone needs to know about Excel.

There are many thousands of users who find that this is all they ever need.

• Add numbers
  
• Subtract numbers
  
• Multiply numbers
  
• Divide numbers

Use simple formulas to do the math



See all Topics