We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

ERROR in VALUE function with text_date as argument [closed]

asked 2020-06-16 22:25:29 +0200

cornouws gravatar image


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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by cornouws
close date 2020-06-20 17:01:15.430495


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).

Opaque gravatar imageOpaque ( 2020-06-16 22:45:10 +0200 )edit

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??

cornouws gravatar imagecornouws ( 2020-06-16 23:01:36 +0200 )edit

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)

Opaque gravatar imageOpaque ( 2020-06-16 23:04:14 +0200 )edit

Clear too - thnx

cornouws gravatar imagecornouws ( 2020-06-20 17:00:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-06-16 23:22:01 +0200

erAck gravatar image

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.

edit flag offensive delete link more


Ah, clear - so it doesn't depend on other language settings, what I tried. thnx

cornouws gravatar imagecornouws ( 2020-06-20 17:00:28 +0200 )edit

Question Tools

1 follower


Asked: 2020-06-16 22:25:29 +0200

Seen: 63 times

Last updated: Jun 16 '20