Correct year for a Work Week

[err504]

There are 2 years for Work Week 52: 2018, and 2019.

My formula in column A which calculates the Work Week apparently fudges the year if the Work Week straddles the end of one year and the start of another.

How do I correct my formula so rows 63 and 64 correctly read 201852?

=IF(D2="","",IF((WEEKNUM((D2-3),21))>9,VALUE(YEAR(D2)&WEEKNUM((D2-3),21)),VALUE(YEAR(D2)&0&WEEKNUM((D2-3),21))))

---

To answer @anon73440385’s comment question: Left to right are Columns A, B, C, & D.

• A=Work Week
• B=Day (of week)
• C=Not relevant (Multiple entries for same date)
• D=Date

D2 doesn’t influence Row 63, Row 2 was the first use of the formula while Row 63 was the first to reveal a problem with the formula as written. D2 influences A2, just as D63 influences A63.

[anon73440385]

What is D2, which columns are shown in the screenshot and how does D2 influence row 63 ?

[mikekaganski]

Likely OP has provided the formula taken from cell A2 (instead of doing that for cell A63), which indeed is confusing. Improving the question for consistency (and not trimming column names) would be nice.

[anon73440385]

Finally I don’t understand - but this is for sure on OPs discretion - why a date 2019-01-01 should show up with an incorrect weeknumber 201852. There may be localization standards to count this way, but I’m no aware of any and LibreOffice isn’t either (tested for all modes function WEEKNUM understands {1, 2, 11, 12, 13, 14, 15, 16, 17, 21, 150})

[mikekaganski]

@anon73440385 wrote:

Finally I don’t understand … why a date 2019-01-01 should show up with an incorrect weeknumber 201852 …

I totally agree. It seems like incorrectly understood intention of ISO 8601 which calls the first week of a year the week with the first Thursday of the year. This could create a wrong impression of some special significance of Thursdays, “justifying” using that weekday as start of week… but actually, coupled with the same standard’s requirement that weeks start only on Mondays, the “first Thursday of a year” rule makes the first week of a year the week with most its days in this year - the goal totally ruined by the “correction” making Thursday the first weekday.

[err504]

It is entirely possible that I implemented ISO 8601 incorrectly. When I created my first work week formula for this spreadsheet I looked up and tried to understand the ISO as intended. But from there it became about matching Work Weeks to Pay Periods so perhaps its a matter of column label, but I’m unaware of another way to implement that. After countless revisions, as many things as I fix or improve, there are often new problems created by the fix or fixes.

I apologize for the confusion caused by the Row numbers and absent column labels. I tried to edit my question once it was pointed out. It’s only my third question here ever and I’m still getting a feel for how best to structure and format my questions.

[mikekaganski]

If you wanted to use strict ISO 8601, you should not have used any corrections to the date (like 3 in WEEKNUM((D2-3),21)), and your simplified year-of-week formula would look like

=IF(D2="";"";VALUE(YEAR(D2-WEEKDAY(D2;3)+3)&TEXT(WEEKNUM(D2;21);"00")))

… or even ISO-defined string like

=IF(D2="";"";YEAR(D2-WEEKDAY(D2;3)+3)&TEXT(WEEKNUM(D2;21);"""W""00"))

which would, of course, give different week numbers to many dates.

[mikekaganski]

You should replace in your formula

YEAR(D2)

with

YEAR(D2-WEEKDAY(D2,14)+1)

to take the year of the start of the current week (i.e., last Thursday, given your formula), instead of year of current date.

[mikekaganski]

By the way, using TEXT(WEEKNUM((D2-3);21);"00") would make your weeknum converted to text to be 2-digit (with optional leading zero), allowing you to exclude one IF with its condition and two almost identical branches, giving much simpler:

=IF(D2="";"";VALUE(YEAR(D2-WEEKDAY(D2;14)+1)&TEXT(WEEKNUM((D2-3);21);"00")))

Even with IF, the only place where it mattered was the additional “0”, so the IF should have only appeared there around the “0”, like in ...&IF(WEEKNUM((D2-3);21)>9;"";"0")&....

[err504]

Thank you for the condensed formula. I am confused about the “14” in the “WEEKDAY(D2;14)”. According to the online documentation, the syntax is “WEEKDAY(Number; Type)” where the options for Type are 1, 2, and 3 only, no 14, yet your formula still works. Perhaps Calc is ignoring the 4 and treating it like Type 1?

[mikekaganski]

I’m afraid, you use obsolete help topics. Here’s the current one.

[err504]

Yikes. Obsolete indeed.