Excel Date Time Issue

Question

question

Swami Premnidhi asked Apr 14, '21 Phil BoBo edited Apr 14, '21

Excel Date Time Issue

Hii,

Trying to convert excel format date that I stored in the global table into standard time.

The idea here is to use the DateTime concept. and subtracting the given date from the start date of the model to get the net time and stored in the label.

Attached the sample model for reference.s1.fsm

what is the problem here and any other way? Please suggest. Thanks.

Software Version:

FlexSim 21.0.4

flexsim 21.0.4 datetime costum code

exceltime.png (102.8 KiB)

s1.fsm (39.2 KiB)

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2021-04-14T19:55:19Z

Phil BoBo answered Apr 14, '21 Phil BoBo edited Apr 14, '21

Excel stores dates and times as the number of days since January 1900.

"15-01-2021" is not an Excel time. That's a formatted date string. If you put that value into a cell in Excel and change the formatting from Date to General, you will see that Excel stores that date as the value 44211. The number 44211 is the "Excel time" representation of the date January 15, 2021.

If you use the "Values Only (very fast)" data distinction mode when importing from Excel to FlexSim, you will get the "Excel time" values for any cells that are formatted as dates. That's what you use the DateTime.fromExcelTime() function to convert.

If you want to construct a DateTime object from a formatted string, use the DateTime constructor that takes a string and a format.

https://docs.flexsim.com/en/21.1/Reference/CodingInFlexSim/FlexScriptAPIReference/Data/DateTime.html#Method-DateTime

DateTime dateTime1 = DateTime("15-01-2021", "%d-%m-%Y");

Also, the model you attached (s1.fsm) is not related to your question or the image you attached.

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

question