Jump to content


Photo

Retrieving the month from a European Style week no

IF custom format WEEKNUM

  • Please log in to reply
1 reply to this topic

#1 Karcy

Karcy

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 09 August 2016 - 08:36 AM

Hi,

 

I am having difficulty retrieving the month based on the year and week number. We use the European week style - week 1 for 2016 started on Monday the  04/01/2016.

 

The following formula retrieves the correct week number -  =WEEKNUM(A2,21)                       A2 is equal to the date

 However the following formula works for 2016 but does not appear to work for prior years-     =CHOOSE(MONTH(DATE(C2,1,B2*7-2)-WEEKDAY(DATE(C2,1,3))),"Jan", "FEB",.......... etc     C2 is equal to year and B2 is equal to week no.

 

Hope you can be some assistance with this.

 

Regards and thanks

 

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 09 August 2016 - 01:09 PM

Hi Karcy,

 

Your formula is returning the correct dates for each week number according to the WEEKNUM function, but you're likely finding that it is returning December for the first week of 2015 because the Monday of that week occurred on the 29th of December 2014. This is technically correct, so you may need to work around this with an IF function if you want it to always return January for the first week.

 

You can see an in-depth explanation of how to use the IF function in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

 

I have attached an example workbook using your formula, along with an IF function applied to return the desired month. It also shows your formula applied to all weeks in 2015 and 2016, proving that the result always matches the result of the WEEKNUM function:

 

Attached File  ReverseWEEKNUM.xlsx   14.37KB   55 downloads

ReverseWEEKNUM.png

 

You might notice that I've also removed the CHOOSE function from your formula and replaced it with a custom format (mmm) that simply displays the Month part of the date. You can see an explanation of custom formats and how to apply them in the video tutorial: Essential Skills Lesson 4-4 Create custom number formats.

 

I hope this is the result you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users