ERROR in VALUE function with text_date as argument [closed]
C:\fakepath\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:
I see the problem that using
VALUE()
makes the whole solution dependent of date recognition and hence your locale. If I useSTRG+SHIFT+F9
to recalculate, I do getErr: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