Exchanging Date Information with a Spreadsheet

In some cases, you may wish to exchange date or time information with a spreadsheet. For example, you may wish to send the current date in the GoldSim simulation to a cell formatted as a date in a spreadsheet, or retrieve a date from a spreadsheet into GoldSim. In order to do so, you must understand how Microsoft Excel (and GoldSim) deal with dates.

Within Excel, a date is stored internally as the number of days (which can be fractional) since December 31, 1899 00:00:00. Hence, if you were to export the number 4.25 into a spreadsheet cell formatted as a date, it would be interpreted as January 4, 1900 at 6:00:00 AM. However, Excel mistakenly adds an extra day into its calendar that did not actually exist (February 29, 1900). As a result, the effective Julian date reference for all times after March 1, 1900 in Excel is actually December 30, 1899 00:00:00.

Within GoldSim, the Run Property DateTime represents the elapsed time since the reference date December 30, 1899 00:00:00. Hence, for all dates after March 1, 1900, Excel and GoldSim have the same effective Julian date reference.

Given this information, the following rules should be followed for exchanging date information between GoldSim and a spreadsheet:

• When exporting a date/time from GoldSim to a spreadsheet cell, you should select Days for the Units in Spreadsheet. If the spreadsheet cell is formatted as a date, it will display as a date; otherwise, it will display the number of days from December 30, 1899 00:00:00 to the specified date that was exported.

• When importing a date from a spreadsheet cell (i.e., a cell formatted in the spreadsheet as a date), you should select Date or Datetime for the Units in Spreadsheet. Within GoldSim, the output from the Spreadsheet element would then be stored as the number of days from December 30, 1899 00:00:00 to the particular date that was imported from the spreadsheet. If running a Calendar Time simulation, the output would be displayed as a date when viewing it (e.g., using a tool-tip) in GoldSim.