Excel - 查找特定日期后特定日期的日期 [英] Excel - Find the date of a specific day after a specific date

问题描述

在Excel中，我需要创建一个公式，让我在会计年度开始之后的第一个星期四的日期。我们的财政年度从10/1开始。所以例如10/1/2016开始一年，我想计算星期四在那个日期之后还是之后，所以我可以在周四以后以编程方式创建一个新的一周。所以，如果星期四下降到10/1，这些行将是10/1，10/8，10/15和。不过10月1日是星期六。第一个星期四之后是10/6/2016如何使用公式找到周四的日期？

In Excel, I need to create a formula that gives me the date of the first Thursday after the beginning of the Fiscal Year. Our Fiscal year starts on 10/1. So for instance 10/1/2016 Begins the year and I want to calculate what the Thursday is on or after that date so I can programmatically create a new week each row on the Thursday after. So, if it Thursday did fall on 10/1 the rows would be 10/1, 10/8, 10/15 and on. However 10/1/2016 is on a Saturday. The first Thursday after that is 10/6/2016 How do I go about finding that Thursday date using a formula?

推荐答案

如果您的会计年度开始在A1，并且您的地区天数从1 =星期日到7 =星期六： -

This should do it if the start of your fiscal year is in A1 and in your locale days are numbered from 1=Sunday to 7=Saturday:-

=A1+MOD(5-WEEKDAY(A1),7)

这在Excel 2007中不适用于我，但应该更容易

This didn't work for me on Excel 2007 but should be even easier

=A1-WEEKDAY(A1,15)+7

在星期日= 1之前的星期几，W可以替换为10 + W，其中W为天数。

The 15 can be replaced with 10+W for other days of the week where W is the day number starting from Sunday=1.

这篇关于Excel - 查找特定日期后特定日期的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！