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?
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
回答1:
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)
This didn't work for me on Excel 2007 but should be even easier
=A1-WEEKDAY(A1,15)+7
The 15 can be replaced with 10+W for other days of the week where W is the day number starting from Sunday=1.