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


  Wednesday, September 30, 2009 – Permalink –

When 28 is 30

How long is a month?


When you use the banker's DAYS360 function to calculate the number of days between two dates, you can get an odd answer.

If you use the DAYS360 function with a start date of February 28 and with an end date of March 28, a value of 28 days is returned.
You expect a value of 30 days to be returned for every full month. (12*30=360)

This behavior may occur if you use the U.S. method, also known as the NASD method, with the DAYS360 function.

To work around this behavior, use the European method with the DAYS360 function. With the European method starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month. To use the European method with the DAYS360 function, use the following syntax: 

=DAYS360(cell number of start date,cell number of end date,TRUE)

Using FALSE or omitting the third parameter uses the U.S. method

Support.microsoft.com
An unexpected value with the DAYS360 function




See all Topics

Labels: , ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment

  Thursday, September 10, 2009 – Permalink –

Trouble with Save?

Also great notification service



You may experience issues when you try to save a Microsoft Excel file if one or more of the following conditions are true:
  • You save an Excel file to a network drive where you have restricted permissions.
  • You save an Excel file to a location that does not have sufficient drive space.
  • The connection to the Excel file has been lost.
  • There is a conflict with an antivirus software program.
  • You save an Excel file that is shared.
  • The 218-character path limitation has been exceeded when you save an Excel file.
  • The Transition Formula Evaluation feature is turned on in Excel.
  • The file was created from a template that contains embedded objects.

How to troubleshoot errors when you save Excel files. KB 271513.
Receive Free Email Alerts every time Microsoft Publishes NEW Support or Knowledge Base Articles! kbAlertz.com is an e-mail notification system that scans the entire Microsoft Knowledge Base every night, and e-mails you when updates or additions are made to the technologies, you subscribe to.
kbAlertz See all Topics

Labels:


<Doug Klippert@ 3:50 AM

Comments: Post a Comment

  Thursday, July 16, 2009 – Permalink –

65,539 Question

The eleventh place error


Sure, you got a recall notice on your new car because the drink holder was the wrong size.

Big deal, Excel 2007 also has/had a problem with some calculations.


The result of the calculation is a number from 65534.99999999995 to 65535. The calculation is performed correctly. However, the result is incorrectly shown as 100000.

The result of the calculation is a number from 65535.99999999995 to 65536. The calculation is performed correctly. However, the result is incorrectly shown as 100001.


Excel 2007 hotfix package

Calculation Issue Update




See all Topics

Labels:


<Doug Klippert@ 5:39 AM

Comments: Post a Comment

  Wednesday, July 15, 2009 – Permalink –

Restore Defaults

Office 2003 redo


To reset the original settings in Office 2003, follow these steps.
Make sure that you back up your files before you follow these steps.
  1. 1. Start any Office 2003 program.
  2. On Help menu, click Detect and Repair.



  3. Click to select the Discard my customized settings and restore default settings check box, and then click Start.
  4. Quit the application, and then click Ignore.
  5. Click OK when you receive the following message:
    Reset of setting to default succeed.

Microsoft Office Diagnostics in 2007 replaces Diagnose and Repair:

Howtogeek.com




See all Topics

Labels: ,


<Doug Klippert@ 3:28 AM

Comments: Post a Comment

  Monday, March 30, 2009 – Permalink –

Linked Table Problems

Lost functionality


Have you found that you cannot update linked Excel tables in Access 2003?


If you have installed Microsoft Office 2003 Service Pack 2 (SP2):


MORE INFORMATION
Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.


Support.Microsft.com:
You cannot change data in linked tables that are to an Excel workbook

You might try reinstalling and then only installing SP1.


Also:

VB123.com:
Guacamole dipped - Access to Excel linked table gotcha

PCWorld.com:
Patent ruling costs Microsoft $8.9 million


"A jury in U.S. federal court found that Microsoft infringed on a Guatemalan inventor's 1994 patent on technology linking the company's Access and Excel programs, and ordered the world's largest software maker to pay $8.9 million in damages.



See all Topics

Labels: ,


<Doug Klippert@ 3:18 AM

Comments: Post a Comment

  Saturday, December 27, 2008 – Permalink –

Mac vs. PC

Spreadsheet vagaries


The 1904 date system and the 1900 date system


"The default date system in Excel 2004 for Mac is the 1904 date system. The default date system in Excel 2003 is the 1900 date system. Typically, the use of different date systems does not cause a problem.

However, if you transfer a workbook from Excel to Excel for Mac, or vice versa, and then copy a date from one workbook to the other, the date may increase or decrease by four years and one day. This issue occurs if the two workbooks use different date systems.

For example, if you copy the date 1/1/1998 from a workbook that uses the 1900 date system and then paste the date into a workbook that uses the 1904 date system, the date appears as 1/2/2002. Alternatively, if you copy the date 1/1/1998 from a workbook that uses the 1904 date system and then paste the date into a workbook that uses the 1900 date system, the date appears as 12/31/1993.

As long as you know about the date systems that your workbooks use, the different date systems should not cause a problem.


KB214330




See all Topics

Labels: , ,


<Doug Klippert@ 3:53 AM

Comments: Post a Comment

  Sunday, December 21, 2008 – Permalink –

Startup Woes

When kickstart is inappropriate


It happens to me less often than in the past, but when Excel does not want to open correctly, here are some resources to solve the problem.

"There are several reasons why you may experience problems when you start Microsoft Excel. This article discusses how to troubleshoot the cause of the startup problem, and possible remedies to correct the problem. The article also contains links to other Microsoft Knowledge Base articles that discuss known startup problems in greater detail."

Support.Microsoft:
How to troubleshoot startup problems in Excel
Also:

Chip Pearson:
Startup Errors In Excel

Contextures:
Starting Excel and Opening Files

JKP Application Development Services:
Fixing Startup problems

John Walkenbach
Solving Common Setup Problems




See all Topics

Labels:


<Doug Klippert@ 3:17 AM

Comments: Post a Comment

  Friday, April 18, 2008 – Permalink –

Excuse Me Your Formula's Showing

Formatting slip



Try this experiment. On a sample worksheet, enter some arbitrary data in say the A1:B5 range.
Select the C column and format it as Text.
(Right click choose Format Cells - on the Number tab choose Text)

In cell C1 enter a function, such as =sum(a1:b1)

With C1 still selected, double click the Fill handle
(the tiny box at the lower right corner of the cell.)


The formula is filled down the column as long as there is data in an adjacent column.


But wait! I don't see the value. I see the formulas!
In addition, the formulas are still in lower case and the relative references have not been updated.


Easy to fix, I hear you say. Just reformat the column as General.


Nothing happens.


To fix the problem, make sure the column is formatted as General.


Select the first cell. Click in the formula bar and hit the Enter key.


Now double click the Fill handle.


(You could also use Edit>Replace to replace = with =. However, Relative references will be incorrect and unless you have reformatted the whole column as General, any new formulas will still display as text.)


The reverse also causes a problem. In a column formatted as General, enter some formulas.

Now reformat the column as Text. The formulas still work, but if you edit one of them, it reverts to a text display.

Microsoft KB:
Cell Linked to Text-Formatted Cell Shows Formula Not Value

Formulas can, of course, be toggled using CTRL+~ (Tilde)
(Though it really should be called CTRL+` (Grave Accent), since the Shift key is not used.)



See all Topics

Labels: , ,


<Doug Klippert@ 7:31 AM

Comments: Post a Comment

  Saturday, January 12, 2008 – Permalink –

Spreadsheet Diet

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 2007

Microsoft Knowledge Base article 211478

Also see:

Spreadsheet Diet

Beyond Technology:
Identifying the Real Last Cell

Microsoft:
Formatting Cleaner Add-in



See all Topics

Labels: , ,


<Doug Klippert@ 8:32 AM

Comments: Post a Comment