Excel公式获取月中的周数(星期一) [英] Excel formula to get week number in month (having Monday)

问题描述

使用 excel 公式，我需要从给定日期获取月中的周数.但是，条件是它应该有星期一.周一到周日是工作日.

Using excel formula I need to get week number in month from a given date. But, the condition is it should have Monday in it. Monday through Sunday is the work days.

我已经试过了:

但是，周数被指定为 5，因为 2013 年 11 月 1 日是星期五，所以它应该是 4，所以它会在 10 月的最后一周计算.

But, week number is given as 5, where as it should be 4 because 1st November 2013 was Friday, so it would be calculated in October's last week.

推荐答案

如果第 1 周总是从该月的第一个 星期一 开始，请尝试使用此公式计算周数

If week 1 always starts on the first Monday of the month try this formula for week number

=INT((6+DAY(A1+1-WEEKDAY(A1-1)))/7)

从 A1 中的日期获取周数，无需中间计算 - 如果您想在 B1 中使用星期一的日期"，您可以使用此版本

That gets the week number from the date in A1 with no intermediate calculations - if you want to use your "Monday's date" in B1 you can use this version

=INT((DAY(B1)+6)/7)

这篇关于Excel公式获取月中的周数(星期一)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！