给定ISO 8601周编号，请在LibreOffice Calc电子表格中获取该周第一天的日期 [英] Given an ISO 8601 week number, get date of first day of that week in LibreOffice Calc spreadsheet

问题描述

LibreOffice Calc电子表格提供函数 ISOWEEKNUM 返回标准的指定日期的ISO 8601周号.

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

我要相反.

➠给定一个标准的星期数，请给我该周第一天的日期(星期一日期).

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

赞:

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

理想情况下，我可以在周一至周日传递1-7的数字，以指定要为其指定日期的星期几.像这样:

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

推荐答案

示例:

公式:

=DATE(B$1,1,$A4*7)+(2-WEEKDAY(DATE(B$1,1,$A4*7)))-7*(ISOWEEKNUM(DATE(B$1,1,1))=1)

• 计算一年中的日期(周数* 7).
• 将日期更改为星期一的工作日.
• 如果一年的第一天是在 第一个ISO周号.
• 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.

这篇关于给定ISO 8601周编号，请在LibreOffice Calc电子表格中获取该周第一天的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！