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



  Sunday, January 28, 2018 – Permalink –

Date Arithmatic

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:42 AM

Comments: Post a Comment


  Monday, January 08, 2018 – Permalink –

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

Labels: , , , , , ,


<Doug Klippert@ 3:57 AM

Comments: Post a Comment


  Thursday, August 10, 2017 – Permalink –

New Excel Web Grabber

Web Toy


"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.
An example scenario: You wish to import and track data from MSN's weather page. Visit the site using the tool, enter Data Capture mode, and select a row or two of data from the table. Then click Select Similar, and the system will find similar data based on your previous selections.

You then can click Import and leverage Excel's rich data-editing capabilities, including the Refresh command, which will revisit the Web page and extract potentially new, updated data."

Web Data Add-In

(Webservices 2013)

From the Excel Blog team


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:57 AM

Comments: Post a Comment


  Monday, May 22, 2017 – Permalink –

Progress Brs

Don't go away



If your macro is going to take a bit of time to complete, it is good manners to inform your users what is going on. If screen updating is turned off, they may think their machine has frozen.

Andy Pope has some great charting examples and also demonstrates a number of
Progress meters

Here's a static example:

Progress display

John Walkenbach, also, has tips about how to create a Progress indicator

also see:
Chip Pearson


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Saturday, October 29, 2016 – Permalink –

Mail Excel

VBA + sample

Excel Guru Ron de Bruin has put together the VBA code needed to send Excel via email.
He has also included the sample workbooks for those that are not VBA literate.

Also:
". . . a new add-in named RDBMail for Excel/Outlook 2007-2016
RDBMail

The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options.
You have the option to send as workbook or PDF for every mail option."
Code to send mail from Excel


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:16 AM

Comments: Post a Comment


  Thursday, May 12, 2016 – Permalink –

Charting Tools

Sample graphs


Ed Ferrero has a number of sample Excel charts and tools.

One he calls a Wind Rose:


How to build a wind rose. Converts compass directions to degrees and radians, and plots a nice-looking chart.


Ferrero Consulting Pty Ltd:
EdFerrero.com/Charting


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:47 AM

Comments: Post a Comment


  Friday, March 06, 2015 – Permalink –

Power Utility Pak

Excel the way you've wanted it


John Walkenbach (J-Walk.com) has improved on the previous versions of PUP. It now includes more than 70 general purpose Excel utilities and 50 worksheet functions.

You can download a 30 day trial at PUP V7

Here are some features just in the area of Chart & Graphics Tools:

Chart Data Labeler: IMPROVED
Lets you apply labels (contained in a range) to a chart data series -- a feature that is surprisingly missing from Excel. Enhancement: Now ignores data that is hidden as a result of autofiltering.
Resize Charts: NEW
Makes it easy to make a chart an exact size, or make all charts the same size.
Convert Chart To Picture: NEW
Makes a copy of a chart as a static picture. This is useful for saving charts that depict various what-if scenarios.
Chart Report: NEW
Produces a detailed report that documents a chart, or produces a report that documents all charts.
Object Align, Size & Space: NEW
Back by popular demand. This utility was removed from PUP v5, but now it's back. It provides an easy way to precisely align, size, and space a group of objects or embedded charts.
Object Hide / Unhide: NEW
An interactive way to hide or unhide charts and other objects on a worksheet.


There is, of course, PUP v7  
Power Utility Pak v6


See all Topics

Labels: , ,


<Doug Klippert@ 3:25 AM

Comments: Post a Comment