error in code when changing from date and time to seconds

[kevin_s7]

[ FlexSim 23.2.1 ]

Greetings community I have a problem when importing an Excel I have a date and time and I want to convert those Excel input data to seconds in my global table but it gives me very high values which should not be, what could be wrong in my code? import source

The times must go to seconds regardless of when the simulation starts running and be able to change the start date and time of the simulation.

code of import

int onreset = param(1); //1 if this code is being called on Reset of the model, 0 otherwise

Table table = Table(Model.find("llegadas (Global Table"));

DateTime ModelStart = Model.dateTime; //get model start time as a number

for (int i = 1; i <= table.numRows; i++){

string StartTime = table[1];

DateTime date = DateTime(StartTime, "%m/%d/%Y "); //get string as a number

table[1] = date - ModelStart;//calculate the difference between start time and arrival time in number of seconds

}

timw.fsm

[JordanLJohnson]

Can you post your Excel file as well?

[kevin_s7]

Datostim2.xlsx

[kevin_s7]

@Jordan Johnson Greetings, I just uploaded my file. I just wanted you to help me correct the code. I don't know what's wrong to import the date and time into seconds and for the time to adapt to the start time.

[JordanLJohnson · Accepted solution]

In Excel, Dates and Times often look like text, but they are usually stored as numbers. If I import your excel data without changing anything, I get the following data:

Notice that all the date values are around 45000. Excel date/times are stored as the number of days since 1900.

FlexSim DateTime objects, on the other hand, count the number of seconds since 1601 (or maybe 1600, I can't recall).

So, knowing this, it is incorrect to try to use the text of the Excel data. Instead, you can use the number. To convert between Excel time and FlexSim time, you can use the convert() command:

DateTime date = DateTime(convert(StartTime, XL_DATETIME, FS_DATETIME));

But there are other problems as well:

• The global table "llegadas" has a space after the name. It is best not to use trailing whitespace like that.
• To access a global table, you just need the following syntax:

  Table table = Table("llegadas");

• To access the start time as a date time, use

  Model.startDateTime

Those are all the issues I've found so far. See if you can get somewhere making those changes. Best of luck!

[kevin_s7]

@Jordan Johnson I enter the lines of code that you provided in the imported code section and I get an error

[moehlmann_fe]

When you click "cancel" the compiler should show you where the error is in the code plus a brief description of what is wrong. (The error can be in a previous line, like a missing semicolon or parentheses, but generally you should be able to spot the mistake quite quickly with that information.)

The code Jordan provided should work though.

Table table = Table(tableName);  // placeholder name
DateTime ModelStart = Model.dateTime; //get model start time as a number
for (int i = 1; i <= table.numRows; i++)
{
    double StartTime = table[1];
    DateTime date = DateTime(convert(StartTime, XL_DATETIME, FS_DATETIME));
    table[1] = date - ModelStart;
}

[kevin_s7]

@Felix Möhlmann Greetings I just entered the code in the import code part but it doesn't work, it should give me in seconds and it gives me a very high value which is not

code1.fsm

[jason_lightfootVL7B4]

Those numbers are the dateTime which is the number of seconds since the year 1601. To see it as a string you need to convert it using the methods of the dateTime class which you'll find in the documentation. You should consider these as absolute values (even though FlexSim does not).

If you need to have it in seconds from the model start time, then you need to subtract one from the other since the difference between two datetimes is a scalar quantity, not an absolute.

[moehlmann_fe]

Jordan's code assumes that the raw values (time in excel format) are imported. Because you have set the Data Distinction to "Automatic" and checked the "Import dates as times as numbers" option, the date values are already converted to a FlexSim datetime on import.

So your code only needs to subtract the start time. Your table is also still called "llegadas ", not "llegadas", as Jordan pointed out. So the for-loop in your code is not actually executed because of the wrong table name.

Table table = Table("llegadas ");
DateTime ModelStart = Model.startDateTime;
for (int i = 1; i <= table.numRows; i++)
{
    table[1] -= ModelStart;
}

[JordanLJohnson]

I did change the data distinction and I forgot to mention it, so no wonder. Also, as long as the both the code window and the Excel Import window are open, I'm fairly confident you have to click "Apply" on both windows to actually apply the code changes.

[kevin_s7]

@Felix Möhlmann Thanks, it's exactly what I wanted.

[kevin_s7]

@Felix Möhlmann One more question, is there a way for the code to remain there and adapt to any table name that I load from Excel without having to change the name of the table from the source code so that the times are updated?

[jason_lightfootVL7B4]

You can create a user command to pass in the table name and column from which you want to subtract the model start time. Then access those values in the command using param(1) and param(2).

[jason_lightfootVL7B4]

Hi @Kevin S7, was Jordan Johnson's answer helpful? If so, please click the "Accept" button at the bottom of their answer. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always comment back to reopen your question.