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.