
|
Book Home Page Bloglines 1906 CelebrateStadium 2006 OfficeZealot Scobleizer TechRepublic AskWoody SpyJournal Computers Software Microsoft Windows Excel FrontPage PowerPoint Outlook Word Host your Web site with PureHost! |
![]() Sunday, January 28, 2018 – Permalink – Date ArithmaticThe drunken cousinWorking 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 excel Labels: Addins, Formulas, General, Macros, Reference, Shortcuts, Tips, Tutorials <Doug Klippert@ 3:42 AM
Comments:
Post a Comment
Monday, January 08, 2018 – Permalink – Web DataNumbers from the etherExcel 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. ![]() Web Data Getting data from the Web in Excel 2013 Web Data YouTube See all Topics excel Labels: Addins, General, Internet, Reference, Shortcuts, Tips, Tutorials <Doug Klippert@ 3:57 AM
Comments:
Post a Comment
Thursday, August 10, 2017 – Permalink – New Excel Web GrabberWeb 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. Web Data Add-In (Webservices 2013) From the Excel Blog team See all Topics excel <Doug Klippert@ 3:57 AM
Comments:
Post a Comment
Monday, May 22, 2017 – Permalink – Progress BrsDon't go awayIf 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: John Walkenbach, also, has tips about how to create a Progress indicator also see: Chip Pearson See all Topics excel Labels: Addins, General, Graphics, Reference, Tips, Tutorials <Doug Klippert@ 3:01 AM
Comments:
Post a Comment
Saturday, October 29, 2016 – Permalink – Mail ExcelVBA + sampleExcel 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-2016Code to send mail from Excel See all Topics excel <Doug Klippert@ 3:16 AM
Comments:
Post a Comment
Thursday, May 12, 2016 – Permalink – Charting ToolsSample graphsEd 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 excel <Doug Klippert@ 3:47 AM
Comments:
Post a Comment
Friday, March 06, 2015 – Permalink – Power Utility PakExcel the way you've wanted itJohn 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:
There is, of course, PUP v7 Power Utility Pak v6 See all Topics excel <Doug Klippert@ 3:25 AM
Comments:
Post a Comment
|