Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal









Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com


  Friday, July 29, 2011 – Permalink –

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
ExcelGames.org  



See all Topics

Labels: , , ,


<Doug Klippert@ 3:27 AM

Comments: Post a Comment

  Wednesday, July 20, 2011 – Permalink –

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

Labels: , , , ,


<Doug Klippert@ 3:13 AM

Comments: Post a Comment

  Friday, July 15, 2011 – Permalink –

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

Labels: , , ,


<Doug Klippert@ 3:27 AM

Comments: Post a Comment

  Thursday, July 14, 2011 – Permalink –

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


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

See all Topics

Labels: ,


<Doug Klippert@ 3:51 AM

Comments:
Hi Doug,

This article was written in 2003 and applies to Office XP. Is there nothing more recent?
# posted by Blogger JP : 11:11 AM
 
I've added a couple of more links. Visual Basic for Applications is pretty timeless
# posted by Blogger Doug Klippert : 7:46 AM
 
Post a Comment

  Friday, July 08, 2011 – Permalink –

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

Labels: , , , , , , ,


<Doug Klippert@ 3:16 AM

Comments: Post a Comment