Handling Dates in Excel Input Data
This article describes how to best handle dates in the Period column of the data template.
Once you have completed the configuration wizard and generated a data template in Data Navigator, you need to fill in the data and you are ready to run your model.
Easy, right? But when you come to the Period column, what format should your date be in?
Background
First, let's look at dates in Excel and AIMMS SC Navigator.
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2020 is serial number 43831 because it is 43830 days after January 1, 1900. You normally don't see those sequential serial numbers in Excel, because you specify that a certain value should be shown in a particular format, and Excel will show it in that format.
However, when AIMMS reads the data, it actually only sees that serial number and is unable to convert that number to a date, unless some assumptions are made about the format. We don't like to make assumptions, unless we really have to, because assumptions never work for every one.
Recommended approach
We recommend that you use a date format in the column Period in most areas your spreadsheet, as this is the most logical way of specifying dates in Excel.
The exception is in the tab Period Definition in column Description, where you should specify the date in none date format.
In the configuration wizard setting "Use Description as display name for Period", select "true".
When you select "true" for this option, AIMMS SC Navigator applications show you the "Period Description", instead of the internal Excel number.
Unless, of course, you'd really like to count the days from January 1, 1900.