从excel中的年份和周数计算月份 [英] calculate the month from the year and week number in excel

问题描述

在Excel 2007中，我有一个年号和周号，我想要计算出月份号。一周是星期一，所以有几个星期会在几年内重叠。

示例：

 code>年份：2012 
第一周开始：1月2日星期一
星期日1月1日在2011年的第52周
  

所以给出如下：

 年份：2011 
周：10 
  

如何在这周工作10，从3月7日开始，因此第10周是在第3个月

感谢您的任何帮助。

解决方案

以下方法

• 确定1月1日的（ISO）周（见此页面）[在单元格 C4 ]

  
  
  

  = INT（（B4-DATE（YEAR（B4-WEEKDAY（B4-1）+4），1,3）+ WEEKD AY（DATE（YEAR（B4-WEEKDAY（B4-1）+4），1,3））+ 5）/ 7）（假设B4 = DATE ]，1,1））




• 计算第1周开始的日期[在单元格 D4 ]

  
  
  

  = IF（C4 = 1，B4-WEEKDAY（B4,3），B4 + 7-WEEKDAY（B4,3））




• 确定相关周的星期一和相应的月​​份

  
  
  

  = MONTH（D4 + 7 *（[Week] -1））

In Excel 2007 I have a Year number and Week number and I want to work out the Month number.

The catch is that in my case the start of every week is a monday, so some weeks will overlap through the years.

Examples:

Year: 2012
Week 1 started: Monday 2nd January
Sunday 1st January was in week 52 of 2011

So given the below:

Year: 2011
Week: 10

How can I work out that week 10 started on 7th March and therefore week 10 was in Month number 3.

Thanks for any help on this.

解决方案

I suggest the following approach

• Determine the (ISO) week of January 1st (see this page) [in cell C4]

  =INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7) (assuming B4 =DATE([Year],1,1))

• Calculate the day on which week 1 starts [in cell D4]

  =IF(C4=1,B4-WEEKDAY(B4,3),B4+7-WEEKDAY(B4,3))

• Determine the Monday of the week in question and the corresponding month

  =MONTH(D4+7*([Week]-1))

这篇关于从excel中的年份和周数计算月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！