I'm looking for an Excel function to return the last Friday in a month for a given date.
我正在寻找一个Excel函数来返回给定日期的一个月的最后一个星期五。
ie: Any date in the month as input will give the date of the last Friday as output.
即:作为输入的月份中的任何日期将给出上周五的日期作为输出。
14-July-09 should give 31-July-09
7-March-05 should give 35-March-09
7 个解决方案
#1
3
Building on @Toomas answer above, I had to make some amendments for Excel 2007, but came up with the following:
基于上面的@Toomas回答,我不得不对Excel 2007进行一些修改,但想出了以下内容:
=EOMONTH(A1,0)-MOD(WEEKDAY(EOMONTH(A1,0))+1,7)
Which is assuming that the WEEKDAY returned for Friday is 6 and then applying a shifted MOD 7 to achieve the correct day.
假设星期五返回的WEEKDAY是6,然后应用移位的MOD 7来实现正确的一天。
#2
2
Try this formula:
试试这个公式:
=IF(WEEKDAY(EOMONTH(A5;0);16)=7;EOMONTH(A5;0);EOMONTH(A5;0)-WEEKDAY(EOMONTH(A5;0);16))
Enter to A5 date value of which you are looking for the last Friday in a month.
输入您要查找一个月中最后一个星期五的A5日期值。
#3
1
The following formula achives this for a date in cell A1:
以下公式为单元格A1中的日期获取此值:
=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)
#4
0
You can try =DATE(YEAR(A1),MONTH(A1)+1,1)-1+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1),-2,-3,-4,-5,-6,0,-1) to find the last friday of the month. Assumes A1 has the date.
您可以尝试= DATE(年(A1),月(A1)+1,1)-1 +选择(周末(日期(年(A1),月(A1)+1,1)-1), - 2, -3,-4,-5,-6,0,-1)找到该月的最后一个星期五。假设A1有日期。
#5
0
I came here looking for the formula to get the most recent Friday in the past which turned out to be =TODAY()-WEEKDAY(TODAY())-1
我来到这里寻找公式,以获得过去最近的星期五,结果是=今天() - 周末(今天()) - 1
#6
0
=EOMONTH(TODAY(),0)-WEEKDAY(EOMONTH(TODAY(),0))-1
This gives the last Friday of this month. You can replace both instances of Today() with any date you like or a cell reference that has a date to get the last Friday in any other month.
这给了本月的最后一个星期五。您可以将Today()的实例替换为您喜欢的任何日期,也可以将具有日期的单元格引用替换为任何其他月份的最后一个星期五。
#7
-1
Awesome! Basically, like trying to find the first or second Monday of the month,
真棒!基本上,就像试图找到该月的第一个或第二个星期一,
=DATE(TheYear,5,1)+CHOOSE(WEEKDAY(DATE(TheYear,5,1)),1,0,6,5,4,3,2)
which returns the first Monday of the month, in this case May
返回该月的第一个星期一,在本例中为May
but to find the last weekday of the month, in the formula, you start with the next month and subtract
但要找到该月的最后一个工作日,在公式中,您将从下个月开始减去
=DATE(TheYear,6,1)-1+CHOOSE(WEEKDAY(DATE(TheYear,6,1)),-6,-5,-4,-3,-2,-1,0)
which returns the last Monday of May
返回5月的最后一个星期一
I have a cell that I named TheYear, in which I enter the year I'm working with, but you can point to whatever cell you have the year you want (e.g. A1).
我有一个名为TheYear的单元格,我在其中进入我正在使用的那一年,但你可以指向你想要的那一年的任何单元格(例如A1)。
I don't know why the pointers to the days of the week are in reverse order but to find the day (e.g. Tuesday instead of Monday), change the order of those numbers but they have to stay in order. For example, 4th Thursday of November:
我不知道为什么指向星期几的指针是相反的顺序,但要找到一天(例如星期二而不是星期一),改变这些数字的顺序,但它们必须保持有序。例如,11月的第4个星期四:
=DATE(TheYear,11,1)+21+CHOOSE(WEEKDAY(DATE(TheYear,11,1)),4,3,2,1,0,6,5)
#1
3
Building on @Toomas answer above, I had to make some amendments for Excel 2007, but came up with the following:
基于上面的@Toomas回答,我不得不对Excel 2007进行一些修改,但想出了以下内容:
=EOMONTH(A1,0)-MOD(WEEKDAY(EOMONTH(A1,0))+1,7)
Which is assuming that the WEEKDAY returned for Friday is 6 and then applying a shifted MOD 7 to achieve the correct day.
假设星期五返回的WEEKDAY是6,然后应用移位的MOD 7来实现正确的一天。
#2
2
Try this formula:
试试这个公式:
=IF(WEEKDAY(EOMONTH(A5;0);16)=7;EOMONTH(A5;0);EOMONTH(A5;0)-WEEKDAY(EOMONTH(A5;0);16))
Enter to A5 date value of which you are looking for the last Friday in a month.
输入您要查找一个月中最后一个星期五的A5日期值。
#3
1
The following formula achives this for a date in cell A1:
以下公式为单元格A1中的日期获取此值:
=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)
#4
0
You can try =DATE(YEAR(A1),MONTH(A1)+1,1)-1+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1),-2,-3,-4,-5,-6,0,-1) to find the last friday of the month. Assumes A1 has the date.
您可以尝试= DATE(年(A1),月(A1)+1,1)-1 +选择(周末(日期(年(A1),月(A1)+1,1)-1), - 2, -3,-4,-5,-6,0,-1)找到该月的最后一个星期五。假设A1有日期。
#5
0
I came here looking for the formula to get the most recent Friday in the past which turned out to be =TODAY()-WEEKDAY(TODAY())-1
我来到这里寻找公式,以获得过去最近的星期五,结果是=今天() - 周末(今天()) - 1
#6
0
=EOMONTH(TODAY(),0)-WEEKDAY(EOMONTH(TODAY(),0))-1
This gives the last Friday of this month. You can replace both instances of Today() with any date you like or a cell reference that has a date to get the last Friday in any other month.
这给了本月的最后一个星期五。您可以将Today()的实例替换为您喜欢的任何日期,也可以将具有日期的单元格引用替换为任何其他月份的最后一个星期五。
#7
-1
Awesome! Basically, like trying to find the first or second Monday of the month,
真棒!基本上,就像试图找到该月的第一个或第二个星期一,
=DATE(TheYear,5,1)+CHOOSE(WEEKDAY(DATE(TheYear,5,1)),1,0,6,5,4,3,2)
which returns the first Monday of the month, in this case May
返回该月的第一个星期一,在本例中为May
but to find the last weekday of the month, in the formula, you start with the next month and subtract
但要找到该月的最后一个工作日,在公式中,您将从下个月开始减去
=DATE(TheYear,6,1)-1+CHOOSE(WEEKDAY(DATE(TheYear,6,1)),-6,-5,-4,-3,-2,-1,0)
which returns the last Monday of May
返回5月的最后一个星期一
I have a cell that I named TheYear, in which I enter the year I'm working with, but you can point to whatever cell you have the year you want (e.g. A1).
我有一个名为TheYear的单元格,我在其中进入我正在使用的那一年,但你可以指向你想要的那一年的任何单元格(例如A1)。
I don't know why the pointers to the days of the week are in reverse order but to find the day (e.g. Tuesday instead of Monday), change the order of those numbers but they have to stay in order. For example, 4th Thursday of November:
我不知道为什么指向星期几的指针是相反的顺序,但要找到一天(例如星期二而不是星期一),改变这些数字的顺序,但它们必须保持有序。例如,11月的第4个星期四:
=DATE(TheYear,11,1)+21+CHOOSE(WEEKDAY(DATE(TheYear,11,1)),4,3,2,1,0,6,5)