Help

Managing Dates in Excel

Excel can have problems when interpreting dates when the date is before 1930 and especially if the date is pre 1900. This problem arises in Excel and not in CliFlo and there are ways around it. Often this issue presents itself as dates in the future.

Microsoft considers that history started on 1-Jan-1900. Even if in CliFlo you choose an Excel date option that has 4 digits as the year Excel is till likely to interpret it as a 2 digit year. CliFlo is not actually downloading a true Excel binary file only a tab delimited text file with a MIME type that tells the operating system to interpret the file as belonging to Excel. There can also be complications if in earlier versions of Excel (particularly in earlier Mac Excel versions) where the 1904 base date system may be used - in this case the year wil be out by 4 (see reference below)

By default any dates being imported that Excel can interpret that are before 1930 get interpreted as the 21st century ie get 100 years added.

If the date is greater than 1900 and the CliFlo date format was an Excel date format that included the 4 digit year "yyyy" then the display date may be corrected. In Excel:

• Select cells containing the dates
• Choose "Format Cells" (from the ribbon or menu
• Choose "Custom" and enter a display date like "d/mm/yyyy h:mm" then "ok"

• choose a date format that Excel doesn't recognise eg 'YYYYMMDD:HH24MI'
• choose a CSV date format
• download as a non-excel format eg text/tab (in the file download options).

• http://support.microsoft.com/kb/214391/en-us
• http://support.microsoft.com/kb/302768.
• http://support.microsoft.com/kb/180162 (1904 base dates)
• http://www.exceluser.com/explore/earlydates.htm
• http://j-walk.com/ss/excel/usertips/tip028.htm.

See also:

• Time Standard
  
• Date/Time Formats
  
• Convert a Cliflo Oracle Date YYYYMMDD:HH24MI to an Excel date