# Correct year for a Work Week

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 @Opaque'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.

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

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.

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})@Opaque wrote:

I totally agree. It

seemslike 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 awrongimpression 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 weekwith most its days in this year- the goal totally ruined by the "correction" making Thursday the first weekday.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.

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... or even ISO-defined

`string`

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