AnyLogic: False number format when exporting data to excel

user_17543219 0
Viewed 64

I collect various data in time plots. If I copy the timeplot data and then paste it into Excel, the number format is often wrong. For example, I often get a date like Aug 94 instead of the actual number from the TimePlot. Unfortunately, I can't easily format this date into a number either, since the formatted number does not match the actual number from the timeplot. If I format the date in the same format as the number above and below, then I get the number 34547. However, this number does not correspond to the actual number of the TimePlot. Anyone know how I can prevent this problem?

enter image description here

anylogic simulation excel
3 Answers

You can only solve this on the Excel side, AnyLogic provides the raw data for you. Excel then interprets stuff. You can test it by pasting the chart raw data into a txt or csv file.

So either fix your Excel settings or paste into a csv, then into an xlsx.

Or better still: Do not manually paste at all. Instead, write your model results into the AnyLogic database and export to Excel from there: this takes away a lot of the pain for you. Check the example models to learn how to do that.

user_21647280

This is not AnyLogic question, rather an Excel & computer formatting problem. One way of resolving this is changing computer's date and time settings.

Another way is to save your output at txt file in AnyLogic. Replace all . with ,. Then open empty Excel, select Text format for the columns. Copy-paste from the txt file.

user_93759790

In Excel there are a few options

  1. when you paste use paste as text only option

enter image description here

But this does not always work as Excel will still try to format the stuff for you

  1. Use the Paste Special option and then choose text

enter image description here

Also possible this will not work, based on your Excel settings.

  1. Paste using the text import wizard (This works for me without fail)

enter image description here

On step 2 choose tab delimited

enter image description here

On step 3 choose Column format as text for every column (you need to select them in the little diagram below)

enter image description here

You will then see the data exactly as it came from AnyLogic. See the example below where I purposefully imported some text which has something that Excel will think is a date. You will now be able to see what in your data made Excel thing your data needed to be formatted the way it is and then you can fix it. (post a new question if you struggle with this conversion)

enter image description here

But as noted by other answers first prize is to write all the important data to external files. But I know that even I sometimes want to export data from a chart and review it in Excel. Option 3 works for me everytime

user_40190940
Related