Date-Time Issue (XLL vs Automation Addin)

Add-in Express™ Support Service
That's what is more important than anything else

Date-Time Issue (XLL vs Automation Addin)
Automation-Addin Date-Time Inconsistency  
Ashim Mishra




Posts: 43
Joined: 2016-03-01
Hi Team,
I have found discrepancy and inconsistency in Automation Addin while parsing datetime cell.

Here are the details:

Xll Addin:
Xll UDF code:

public static string GetDateString(string inputString)
            {
                return inputString;
            }




Automation Addin:


Automation Addin UDF code:

public static string GetDateString(string inputString)
            {
                return inputString;
            }




Result for date "January 04 2020":

a) When thread culture is set to "en-US" in the contructor
Xll Addin:
a.1) cell A1 when the system culture is en-US -> 4/1/2020 the output for the udf -> =GetDateString(A1) is -> 43922
a.2) cell A1 when the system culture is en-EU -> 01/04/2020 the output for the udf -> =GetDateString(A1) is -> 43922

Automation Addin:
a.3) cell A1 when the system culture is en-US -> 4/1/2020 the output for the udf -> =GetDateString(A1) is -> 4/1/2020
a.4) cell A1 when the system culture is en-EU -> 01/04/2020 the output for the udf -> =GetDateString(A1) is -> 4/1/2020


b) When culture is not set in the contructor:
Xll Addin:
b.1) cell A1 when the system culture is en-US -> 4/1/2020 the output for the udf -> =GetDateString(A1) is -> 43922
b.2) cell A1 when the system culture is en-EU -> 01/04/2020 the output for the udf -> =GetDateString(A1) is -> 43922

Automation Addin:
b.3) cell A1 when the system culture is en-US -> 4/1/2020 the output for the udf -> =GetDateString(A1) is -> 4/1/2020
b.4) cell A1 when the system culture is en-EU -> 01/04/2020 the output for the udf -> =GetDateString(A1) is -> 01/04/2020

c) When Custom culture is set in the contructor where date format = system date format and remaining settings are inherited from en-US culture.
c.1) cell A1 when the system culture is en-US -> 4/1/2020 the output for the udf -> =GetDateString(A1) is -> 43922
c.2) cell A1 when the system culture is en-EU -> 01/04/2020 the output for the udf -> =GetDateString(A1) is -> 43922

Automation Addin:
c.3) cell A1 when the system culture is en-US -> 4/1/2020 the output for the udf -> =GetDateString(A1) is -> 4/1/2020
c.4) cell A1 when the system culture is en-EU -> 01/04/2020 the output for the udf -> =GetDateString(A1) is -> 4/1/2020

Contructor (xllmodule.cs/addinmodule.cs) code for case c:

				CultureInfo usCulture = new CultureInfo("en-US");
                CultureInfo cloneCulture = (CultureInfo)usCulture.Clone();
                cloneCulture.DateTimeFormat = CultureInfo.CurrentCulture.DateTimeFormat;
                Thread.CurrentThread.CurrentCulture = cloneCulture;
                Thread.CurrentThread.CurrentUICulture = cloneCulture;
				InitializeComponent();
				// Please add any initialization code to the AddinInitialize event handler


My questions are:
1) Why Automation addin returns inconsistant value (a.4, b.4, c.4) where as xll returns value in OA format and is consistant?
2) Why Automation addin is not returning value in oa date format?
3) Even after loading correct custom culture with system date format, why automation addin returns value in en-US date format (refer c.4) ?
4) How to resolve date inconsistancy in automation addin?
Posted 23 Jul, 2020 04:55:44 Top
Andrei Smolin


Add-in Express team


Posts: 17288
Joined: 2006-05-11
Hello Ashim,

Can the issue relate to your having more than one add-in (UDF and/or COM) enabled? Setting the culture in a constructor may not help if another add-in changes the culture on the fly.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 23 Jul, 2020 05:58:13 Top
Ashim Mishra




Posts: 43
Joined: 2016-03-01
Hey Andrei,
We have only one automation-addin and the behaviour is inconsistent.
The above code is for the reference so that you could reproduce the same (if required).
Here we are comparing the behavior of automation-addin with the xll-addin separately under the same culture and excel settings.
Posted 23 Jul, 2020 06:29:44 Top
Andrei Smolin


Add-in Express team


Posts: 17288
Joined: 2006-05-11
Hello Ashim,

1. Your GetDateString functions must have different names.
2. Let them accept and return an Object rather than string.

Does the behavior changes?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 23 Jul, 2020 09:38:04 Top