Excel datetime format issue, possibly locale dependent

Question

I'm working on a Belgian Excel 365 workbook. I have just imported a list of dates, looking like this:

dinsdag 5 december 2023 11:55:55
dinsdag 5 december 2023 11:55:56
dinsdag 5 december 2023 11:55:57

("dinsdag" means Tuesday, the rest is obvious)

Excel does not recognise this as being a datetime, so I though of turning a datetime into that format, and use the description of that format in order to explain Excel what to do.
Unfortunately, this seems not to work: in two other cells, I have typed Ctrl; and modified the time to "12:34:56", and this is what I see:

(Cell formatting is set as [$-x-sysdate]dddd, mmmm dd, jjjj u:mm:ss, where j stands for "jaar" ("year") and u for "uur" ("hour"))

The cell underneath ("E3") has cell formatting u:mm:ss.

In my opinion, this makes no sense: cell "E3" clearly states that cell formatting u:mm:ss shows the time as "12:34:56", so why don't I see the timestamp in cell "E2"? (For your information, I've enlarged the cell but I still did not see the timestamp)

Also, once I have this correct formatting, is there a function, like =DateFromText() I can use, based on the formatting, something like:

=DateFromText(A1, "[$-x-sysdate]dddd, mmmm dd, jjjj u:mm:ss")

Thanks in advance

Answer 1

Try this: =--REPLACE(A1,1,FIND(" ",A1),)

It removes "Dinsdag" (or whatever "-dag") from the string and converts it to numberical value.

You can then format it to your needs, for instance: dddd d mmmm e hh:mm:ss

I'm uncertain this works for all system languages. Let me know if this works for you.

Answer 2

There's probably a better way to do this, but you can use a custom LAMBDA function to convert your text into an actual date:

=LAMBDA(_input, LET(
_array, TEXTSPLIT(_input, " "), 
_day, INDEX(_array, 2),
_month, MATCH(INDEX(_array, 3), {"januari","februari","maart","april","mei","juni","juli","augustus","september","oktober","november","december"}, 0),
_year, INDEX(_array, 4),
_time, INDEX(_array, 5),
DATE(_year, _month, _day)+TIMEVALUE(_time)
))

Or if your system separator is a ";" instead of a "," :

=LAMBDA(_input; LET(
_array; TEXTSPLIT(_input; " "); 
_day; INDEX(_array; 2);
_month; MATCH(INDEX(_array; 3); {"januari";"februari";"maart";"april";"mei";"juni";"juli";"augustus";"september";"oktober";"november";"december"}; 0);
_year; INDEX(_array; 4);
_time; INDEX(_array; 5);
DATE(_year; _month; _day)+TIMEVALUE(_time)
))

To add this function, simply go to the name manager and add a new named range, with the formula as its value:

Then, simply call the function in your spreadsheet:

Note that the result is a real date/time, you can change the display format to your liking.

The function works like this: it takes a text as a parameter and splits it into an array, using the space as a separator. So the array contains:

1. The name of the day (you don't need it)
2. The day as a digit
3. The name of the month
4. The year as a digit
5. The time in hh:mm:ss format

Using the INDEX function to access these 5 values, you can recreate a date using the DATE() function and convert the time in text format in actual time format by using the TIMEVALUE() function. Adding a DATE and TIMEVALUE creates a date/time.

Note that the month name needs to be converted into a digit, by using the MATCH function. I manually added the names in Belgian, but it's easy to edit the 12 values to match any language.

More info on the lambda function: https://exceljet.net/functions/lambda-function