4

LibreOffice Calc spreadsheet offers a function ISOWEEKNUM to return the standard ISO 8601 week number of the specified date.

I want the opposite.

➠ Given a standard week number, give me the date of the first day of that week (the Monday date).

Passing integers is acceptable. Also nice if able to pass a string in standard format.

Like this:

DATE_OF_ISOWEEKNUM( 2017 , 42 ) ➝ date of Monday of week 42 in week-based year 2017
DATE_OF_ISOWEEKNUM( "2017-W42" ) ➝ date of Monday of week 42 in week-based year 2017

Ideally, I would be able to pass a number 1-7 for Monday-Sunday to specify the day-of-week for which I want a date. Something like this:

DATE_OF_ISOWEEKNUM( 2017 , 42 , 1 ) ➝ date of Monday of week 42 in week-based year 2017
DATE_OF_ISOWEEKNUM( "2017-W42-1" ) ➝ date of Monday of week 42 in week-based year 2017

DATE_OF_ISOWEEKNUM( 2017 , 42 , 7 ) ➝ as above, but Sunday
DATE_OF_ISOWEEKNUM( "2017-W42-7" ) ➝ as above, but Sunday
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • User `keme` gives a set of formulas to do this at https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=14602. Are you looking for a single function? If so, `DATE_OF_ISOWEEKNUM` could be implemented with a macro. – Jim K Jul 30 '17 at 00:11

1 Answers1

3

Example:

enter image description here

Formula:

=DATE(B$1,1,$A4*7)+(2-WEEKDAY(DATE(B$1,1,$A4*7)))-7*(ISOWEEKNUM(DATE(B$1,1,1))=1)
  • Calculate the date of day (weeknumber * 7) in the year.
  • Correct the day to be weekday Monday.
  • Correct to 7 days before, if the first day of the year is in the first ISO weeknumber.
Axel Richter
  • 56,077
  • 6
  • 60
  • 87