0

The data in a pivot table is displaying wrong week numbers for given dates, essentially the weeknum is ahead by 1 week,

enter image description here

Thanks in advance

Improve this question
6
  • @HafizSb No. 11-Oct-21 is week 41. Commented Aug 24, 2021 at 11:09
  • imgur.com/SaMf9qL Excel 2016 is showing 42 @Gravitate Commented Aug 24, 2021 at 11:27
  • 1
    @HafizSb It shows as week 41 on mine. Could this depend on the date/time settings on the local machine? I thought ISOWEEKNUM was the same everywhere? Commented Aug 24, 2021 at 11:31
  • @HafizSb Ah... you are using WEEKNUM and I am using ISOWEEKNUM. Not entirely sure of the difference, except WEEKNUM defaults to Sunday being the first day of the week, where as ISOWEEKNUM is a Monday. I am not sure what the OP is using but I suggest they try the opposite of what they currently are. Commented Aug 24, 2021 at 11:36
  • Function ISOWeeknum is used to return the week number (for a specified date) in the year. It follows the ISO (International organization for Standardization) week number. =ISOWEEKNUM("1/6/2019") and Function Weeknum is used to return the week number (for a specified date) in the year. It has 2 parameters and the 2nd parameter is used to define the weeks beginning on specific day. =WEEKNUM("01/6/2019",1) - Here 1 is used for Sunday as 1st day of week. You are right @Gravitate Commented Aug 24, 2021 at 11:40

2 Answers 2

Reset to default
1

You haven't shared your raw data but I assume you have used the WEEKNUM function.

I think the problem you are having is due to using WEEKNUM instead of ISOWEEKNUM.

WEEKNUM starts counting weeks from the very first day of the year, which results in not every week having 7 days.

ISOWEEEKNUM ensures every week has 7 days but does mean that one week can run over 2 years (which can cause it's own issues).

Please see the table below showing how the results of the two functions vary:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

pivot tabel is displaying the wrong weeknumber, even though I’m using ISOWEEKNUM in my data: Jan 1st, 2023 was a Sunday, and since ISOWEEKNUM uses the first week of the year that contains a Thursday week number 1 starts on Jan 2 (Monday). Yet when I create a pivot it starts with week 2. Or, rather, displays week 2. It’s a bug in the pivot table, still here in the latest version of Excel (also for the Mac btw)

Improve this answer

1 Comment

Could be a comment, not an answer. Not so clear, also the formatting is not helping.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.