|
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! 
|
 
Friday, April 20, 2018 – Permalink – Week NumbersWho'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. 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 excel Labels: Formulas, General, Reference, Shortcuts, Tips, Tutorials <Doug Klippert@ 3:25 AM
Comments:
Post a Comment
Wednesday, April 04, 2018 – Permalink – Camera ToolSmile!(This is the pre-2007 routine. For 2007+ see the end. ) To create a linked picture of part of a spreadsheet for use elsewhere:
 If the Camera tool is on your toolbar:
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 excel Labels: Gadgets, General, Graphics, Reference, Shortcuts, Tips, Tutorials <Doug Klippert@ 3:16 AM
Comments:
Post a Comment
Thursday, February 15, 2018 – Permalink – Match Format PasteCopy/Paste formatting in Word, PowerPoint or ExcelWhen 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 excel <Doug Klippert@ 3:57 AM
Comments:
Post a Comment
Wednesday, February 14, 2018 – Permalink – Spreadsheet BloatFormatting bloatAvoid 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 excel Labels: Customize, General, Reference, Shortcuts, Tips, Tutorials <Doug Klippert@ 3:59 AM
Comments:
Post a Comment
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
Wednesday, January 24, 2018 – Permalink – Reset Shortcut MenuContext menu redoFor 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 excel <Doug Klippert@ 3:04 AM
Comments:
Post a Comment
Friday, January 19, 2018 – 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. |
