Excel Date switches between EU and US notation

[k.hendrickx]

Hi all,

We have a project management script that takes the current date and then writes this to an excel document. Now most of the time this works okay, but at the beginning of each month the dates get switched from EU style to US style (dd/MM/yyyy -> MM/dd/yyyy)

I already put in a small message box to verify if the date string is correct, and for as far as I can tell Inventor and iLogic are doing okay (the string follows the dd/MM/yyyy notation).

However, when I open the excel pretty much all dates up to 12/.../2024 are messed up to MM/dd/yyyy.

Next guess was that Excel is dropping the ball, but when I change the cell type it actually gives me the wrong full date.

Example, a project from the 9th of December 2024 -> iLogic gets the current date as a string -> message box shows "9/12/2024", I open the Excel file and it shows "12/09/2024" in the cell and when I change it to a long notation it becomes "12 September 2024".

Does anyone know where the translation between the string in Inventor and the content of the cell in Excel goes wrong?

Snippet of code regarding the date function:

' Get the current date and start time
	Dim currentDate As String = Now.ToString("dd/MM/yyyy")
	MessageBox.Show(currentDate, "currentDate")

....
' Main code to write data to Excel
	Dim excelApp As Object
	Dim excelWorkbook As Object
	Dim excelSheet As Object

	' Open Excel application and workbook
		excelApp.Visible = False ' Excel stays in the background
		excelWorkbook = excelApp.Workbooks.Open(filePath)
		excelSheet = excelWorkbook.Sheets(1) 
		' Find the last used row in the sheet
		Dim lastRow As Integer
		lastRow = excelSheet.Cells(excelSheet.Rows.Count, 1).End(-4162).Row + 1 ' -4162 is xlUp

		' Write data to Excel in the specified order
		excelSheet.Cells(lastRow, 1).Value = currentDate                   ' Date
' Save and close Excel
		excelWorkbook.Save()
		excelWorkbook.Close()
		excelApp.Quit()

This results in this:

I have already though about adding a " ' " in front of the string so that Excel is forced to use it as plain text, but I'd like to use these date for analyzing purposes, so I'd rather keep them in a date format.

[mslosar]

I know excel stores dates as dates. If you type in 12/25/2024 in text, it converts it to a date and excel will display that date based on the localization settings of your computer.

You might need to explicitly format the cell as pure text so it stores as text and does not convert it to a date. Weird that it only seems to affect some dates and not others. My first thought would be does the excel file have any vba in it that could be doing this? I wouldn't imagine localization settings change some formats but not all.

[k.hendrickx]

To be clear, all formatting on the PC and excel (as far as my knowledge of Excel goes) should be set to European regional (dd/MM/yyyy). Which seems to be the same as the string iLogic fetches from the Now function. Just seems that for a bizarre reason Excel interprets the dd/MM/yyyy string as an US regional date.

The only reason why it doesn't do that with the dates beyond the 12th of a month simply is because they don't exist in US notations.

The file itself is just a relatively new file with no conditioning or VBA code. Just plain rows and columns.

To be clear, I'm fairly sure this issue has more to do with Excel then iLogic, but i tried taking a long shot here.

[mslosar]

Interesting. I just pulled up excel and entered:

12/5/2024

30/12/2024

I'm in the US, and it treated 12/5 as a date and 30/12 as text.  Is it possible it's doing the same thing? or something similar and making one text and one a date?  I only picked up on it because i didn't align the columns, if i had aligned the column with justification, it would have all aligned and I wouldn't have noticed.

Also found this:

If you want to ensure Excel interprets dates consistently regardless of regional settings:

• Use an Unambiguous Format:
  • Enter dates in ISO 8601 format (YYYY-MM-DD), e.g., 2024-11-30. Excel will interpret this correctly across all regions.
• Pre-format the Column as Text:
  • If you are importing data with ambiguous dates, format the column as "Text" first to prevent Excel from making automatic assumptions.

[k.hendrickx]

Thanks for the response. That actually makes sense and it might very well be that all the dates beyond the 12th are just texts.

I'd rather not change all the data to plain text because I'd like to keep the ability to do analysis on this data.

I'll try rewriting the code to enter the dates in that ISO format which might be the best choice.

I'll try tomorrow and update this thread.

[k.hendrickx]

okay so now things are just being weird...

So first of all I changed the code to get a yyyy/MM/dd date.

I removed all the data from my testing Excel file and configured the date column as Date with the "yyyy-MM-dd" type:

So let's test it... the current date is the 20/12/2024, so the script fetches the following string "2024/12/20" which is confirmed by my debug messagebox.

The following line gets added in the Excel file:

So far everything is looking good. I get the right date and don't have to convert it to a text format for future purposes.

Off course this date worked fine before as well because 20/12/2024 doesn't exist in an US date format.

So to test an earlier date I disabled the Now() function and just fed a string as a date ("2024/12/06").

Again the debug message box confirms that the value of currentDate = "2024/12/06".

Back to Excel, and this gets filled in:

again it gets "converted" to the 12th of June... 😩

What I also notice it that both dates are not text values, but are both the numerical value of the date

(<- converted to number values)

So apparently Excel is still misinterpreting the value it's receiving from iLogic and I can't seem to be able to just force Excel to stop interpreting the value as the wrong date.

*edit*: 1 more experiment I just tried is just typing random dates in the excel directly to see how it manages converting those.

I tried:

- 2024/12/06

- 06/12

- 06-12-2024

and all of these immediately got converted to the correct 2024/12/06 date.

So here it again seems Excel is actually working fine in handling the data that gets entered (manually), iLogic is handling it correctly in getting and storing the values... But somewhere in between iLogic and Excel things get tangled up.

[k.hendrickx]

Nobody has any more ideas what might be causing this issue?

Still haven't figured out how to control the date format between iLogic and Excel.

One of the final ideas I might have is to convert the date to an integer that matches with Excels date format system, but surely there must be an easier solution than that?

wkr

[k.hendrickx · Accepted solution]

Okay kept cracking at it myself and I think I might have found a solution myself.

By changing the type of date the Inventor fetches, I've been able to transfer the correct date to excel (so far).

' Get the current date and start time
	Dim currentDate As Date = Now.ToShortDateString()

This no longer gets a string value, but a Date object (which I didn't know existed).

Now when I write this to Excel I get the follow data:

which works fine for my intent.

So conclusion, don't use 

Dim currentDate As String = Now.ToString("dd/MM/yyyy")

but instead use the line above.