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,其中应该是4,因为2013年11月1日是星期五,所以它将在上周的十月份计算。
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屋!