During query execution, optimal(xl) can recognize date/time data in TEXT or VARIANT columns and convert those values to Excel DateTime types on-sheet.

Text-to-type conversions can be turned off or customized via optimal(xl)'s menu:

≡optimal(xl)∟Settings∟Text‑to‑type conversions

Unlike Excel's aggressive, cell-by-cell auto-formatter, optimal(xl) uses a cautious, 'all-or-nothing', columnar approach to free you from the errors and aggravation that arise from misinterpreted textual dates.

For the TEXT or VARIANT columns in your select statement to be converted into date/times on-sheet, the following conditions must be met:

1. If your date/time string includes a date, the date must have a 4-digit year.

2. A single date/time format must parse all date/time lookalikes in the column. For example, if your Snowflake query column includes the text values 2024‑Sep‑30 and Sep‑30‑2024, no type conversions will be made and the entire column will land on-sheet as stable text, not Excel dates.

   Non-date/time text (e.g., N/A, inception, YTD) in the same column as textual date/time values (e.g., September 30, 2024) will not prevent optimal(xl) from recognizing dates.

   However, any value in the same column that can be interpreted as a number (e.g., USD 405.12, (45), 10%, 1e6) will prevent optimal(xl) from recognizing any dates in the column and the entire column will land on-sheet as stable text. This is because Excel date/times are, under the surface, represented as numbers and therefore if optimal(xl) formatted your on-sheet column as date/times, any non-date/time numbers present would be incorrectly coerced into date/times, e.g.,

   3.14159 → 1/3/1900  3:23:53 AM

   As a corollary to this constraint, if any date/time string in your column is only a time, e.g., 16:45:32, any other value in the column with a date component will cause all date/time values to be rendered as stable text. Also, optimal(xl) will not presume a zero-time. If any date/time strings in a query column have a time component, e.g., 2024‑09‑30 13:45, date strings like 2024‑09‑30 won't be parsed; they must explicitly denote a zero time, i.e., 2024‑09‑30 00:00.

3. If your textual date/time values are in a potentially ambiguous format like MM/dd/yyyy or dd/MM/yyyy (e.g., 09/08/2024) then your query must return sufficient values to conclusively resolve the correct format. I.e., the query:

   select '09/08/2024'

   will land on-sheet as stable text while the query:

   select '09/08/2024'
   union
   select '12/31/2024'

   will land on-sheet as Excel dates. This is because the value 12/31/2024 resolves the ambiguity over which is the month part and which is the day part.

4. All textual dates must be on or after Excel's cell-entry epoch (1900‑01‑01, or 1904‑01‑01 when using Excel's alternative 1904 date system). Any dates before the epoch will cause the entire column to be rendered as text. This condition can be removed via the optimal(xl) menu:

   ≡optimal(xl)∟Settings∟Data type defaults

   Here you can select to convert dates before the epoch to #VALUE! errors which will allow the other, valid, textual dates in the selected column to be converted to Excel dates.

5. Textual month intervals, i.e., date strings that are without a day part, (e.g., September 2024, or a KDB+ month interval, 2024.09m), will cause the entire column to land on-sheet as stable text unless you have explicitly allowed month-interval date strings to be mapped to the first day of the month, via optimal(xl)'s settings.

   September 2024 → 2024-09-01

6. Your textual date/time data must be in a format that is included in the tables at the bottom of this page. The formats listed here assume US/English regional settings. The formats available to you may change slightly based on your own regional settings and the version of .Net on your machine. A precise list of these formats can be found via optimal(xl)'s menu:

   ≡optimal(xl)∟Settings∟Auto type conversions∟Supported date/time formats

To make the table of supported formats below more digestible, each row that has a format with a time component represents many variations of time zone syntax and fractional second precision which are not explicitly listed.

These variations are described in this section. For example, in the bottom table, the textual value:

13:45

implicitly covers 5 distinct time zone variations:

None

13:45

UTC ('Z' suffix designator)

13:45Z

UTC hour offset (w/o leading zero)

06:45-7

UTC hour offset (w/ leading zero)

06:45-07

UTC hour and minute offset (w/ leading zero)

06:45-07:00

Each format with a time part which contains seconds has 14 additional implicit variations to cover 1 to 7 fractional second decimals and two decimal separators, ('.' & ','). For example, when you see this format:

13:45:38

it is implied that that following formats will also be parsed:

13:45:38.6
13:45:38.63
13:45:38.630
13:45:38.6305
13:45:38.63050
13:45:38.630504
13:45:38.6305045
13:45:38,6
13:45:38,63
13:45:38,630
13:45:38,6305
13:45:38,63050
13:45:38,630504
13:45:38,6305045

Each of these variations also have the 4 additional time zone formats described above. In other words, in the table below, the format:

13:45:38

represents all of the following 80 variations:

13:45:38
13:45:38.6
13:45:38.63
13:45:38.630
13:45:38.6305
13:45:38.63050
13:45:38.630504
13:45:38.6305045
13:45:38Z
13:45:38.6Z
13:45:38.63Z
13:45:38.630Z
13:45:38.6305Z
13:45:38.63050Z
13:45:38.630504Z
13:45:38.6305045Z
06:45:38-7
06:45:38.6-7
06:45:38.63-7
06:45:38.630-7
06:45:38.6305-7
06:45:38.63050-7
06:45:38.630504-7
06:45:38.6305045-7
06:45:38-07
06:45:38.6-07
06:45:38.63-07
06:45:38.630-07
06:45:38.6305-07
06:45:38.63050-07
06:45:38.630504-07
06:45:38.6305045-07
06:45:38-07:00
06:45:38.6-07:00
06:45:38.63-07:00
06:45:38.630-07:00
06:45:38.6305-07:00
06:45:38.63050-07:00
06:45:38.630504-07:00
06:45:38.6305045-07:00
13:45:38
13:45:38,6
13:45:38,63
13:45:38,630
13:45:38,6305
13:45:38,63050
13:45:38,630504
13:45:38,6305045
13:45:38Z
13:45:38,6Z
13:45:38,63Z
13:45:38,630Z
13:45:38,6305Z
13:45:38,63050Z
13:45:38,630504Z
13:45:38,6305045Z
06:45:38-7
06:45:38,6-7
06:45:38,63-7
06:45:38,630-7
06:45:38,6305-7
06:45:38,63050-7
06:45:38,630504-7
06:45:38,6305045-7
06:45:38-07
06:45:38,6-07
06:45:38,63-07
06:45:38,630-07
06:45:38,6305-07
06:45:38,63050-07
06:45:38,630504-07
06:45:38,6305045-07
06:45:38-07:00
06:45:38,6-07:00
06:45:38,63-07:00
06:45:38,630-07:00
06:45:38,6305-07:00
06:45:38,63050-07:00
06:45:38,630504-07:00
06:45:38,6305045-07:00