Dates in Sheet Imported from Enable Early By One Day

ID: Q125852

The information in this article applies to:

Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, if you import a spreadsheet that was created in Enable, date values in the imported sheet may differ from the original sheet by one day.

CAUSE

Cells that are formatted in Enable as dates and contain a date values later than February 28, 1900, will be imported to Microsoft Excel with the date one day early. For example, March 30, 1990, is imported as March 29, 1990.

WORKAROUND

To work around this problem, you can create a Visual Basic procedure that finds any date later than February 28, 1900, and adds one day to it.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following Visual Basic procedure finds all cells containing a date later than February 28, 1900, and adds one day to it. To create an run this macro, do the following:

1. In a new Visual Basic module, enter the following code:

      Sub AddADay()
      'loops through each cell in selected range
      For Each Cell In Selection
        'Checks to see if cell contains a date
        If IsDate(Cell.Value) = True Then
         ' check to see if the serial number is
         ' greater than 59 (the serial number for
         ' 2/28/1900
            If Cell.Formula > 59 Then
                'Adds 1 day to the existing date
                Cell.Formula = Cell.Formula + 1
            End If
         End If
      Next
      End Sub

2. Select any range in the spreadsheet.

3. From the Tools menu, choose Macro.

4. In the Macro dialog box, select AddADay, and then choose Run.

MORE INFORMATION

Enable is manufactured by Tandem Computers, Inc., a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this products' performance or reliability.

REFERENCES

For more information on importing Enable files choose Search in the Enable Conversion Help File and type:

   Spreadsheet Files

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a

Keywords          : xlwin 
Version           : 5.00 5.00c 7.00 7.00a
Platform          : WINDOWS

Last Reviewed: June 1, 1999