Ask Your Question
0

Correct year for a Work Week

asked 2019-05-13 09:56:16 +0200

err504 gravatar image

updated 2019-05-13 10:54:38 +0200

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.

image description

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.

edit retag flag offensive close merge delete

Comments

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

Opaque gravatar imageOpaque ( 2019-05-13 10:19:38 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-05-13 10:33:11 +0200 )edit

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 gravatar imageOpaque ( 2019-05-13 10:48:47 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-05-13 12:07:20 +0200 )edit

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.

err504 gravatar imageerr504 ( 2019-05-13 12:35:21 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-05-13 12:51:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-13 10:22:10 +0200

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.

edit flag offensive delete link more

Comments

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-05-13 10:45:52 +0200 )edit

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?

err504 gravatar imageerr504 ( 2019-05-17 14:23:08 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-05-17 15:07:09 +0200 )edit

Yikes. Obsolete indeed.

err504 gravatar imageerr504 ( 2019-05-17 16:17:37 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-13 09:56:16 +0200

Seen: 29 times

Last updated: May 13