Spreadsheet_VALUE_from_text_date_ERROR.ods
I’ve created a neat formula that creates a week number from some text date/time:
=WEEKNUM(VALUE(LEFT(B2;FIND(" “;B2; FIND(” ";B2)+1)+4));1)
However, that fails for e.g. January 7, 2020 10:49 am UTC
but does work fine for April 19, 2020 11:08 pm UTC
And when doing with date-texts in Dutch, it works OK …
I can’t figure out why 
Help appreciated - attached sheet shows it all.
What is wrong with January 7, 2020 being week 2 (and 43837 is the correct number for that date)?
You selected mode=1 in WEEKNUM() which means week of January 1, 2020 is week 1 and week beginning on Sunday. And this week is from 2019-12-29 (Sunday) - 2020-01-04 (Saturday) and week 2 is from 2020-01-05 until 2020-01-11 (according to your selection).
Ah nice - it does show that for me in row 9, Rows 3 and 4 show Err:502
I’ve tried in three different versions (6.0.7, 6.4.0, 7.1_master
So prolly something with my Language- or Calculate- or Formula-settings??
I don’t see any error - it works correctly for me.
Tested using LibreOffice:
Version: 6.4.4.2, Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded
I see the problem that using VALUE() makes the whole solution dependent of date recognition and hence your locale. If I use STRG+SHIFT+F9 to recalculate, I do get Err:502 as well (on a de_DE locale - January is not a valid month name and removing the y makes it a valid month name and the date recognition starts working)
Clear too - thnx
Whether the text January 7, 2020 is recognized as a valid date or not entirely depends on the current locale setting. It is a valid date in en-US, but already not in en-GB, and certainly not in nl-NL Dutch locale.
Ah, clear - so it doesn’t depend on other language settings, what I tried.
thnx