ERROR in VALUE function with text_date as argument


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 :slight_smile:

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:, 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.