How do I get next Thursday from a random date?

问题:

What I want to do is so you have a list of random dates, it will never be the same so it has to be a universal formula, but you get any date, any date of the year and then every Thursday is the Starting of a new week. For each date you set the date equal to the Thursday that it is greater than the previous and less than the Thursday.

Example to clarify since I am being vague, say you get June 24th, 2012. June 24th is greater than June 21st, 2012 (which is the previous Thursday) and it is less than June 28th, 2012, which is the next Thursday. This means it should generate the value June 28th, 2012 in the column next to it.

Is that making any sense? Based on any date value you get, you calculate which Thursday date it should generate? Any way to do this in excel?

回答1:

This formula will give you the Thursday following a date in A1

=A1+7-WEEKDAY(A1+2)

If A1 is a Thursday it returns that date, if it should be A1+7 if A1 is Thursday then change to this version

=A1+8-WEEKDAY(A1+3)

回答2:

=IF(WEEKDAY(A1) < 5, A1+5-WEEKDAY(A1), A1+5+7-WEEKDAY(A1))

Will give you the next Thursday from the date given in Cell A1 in Excel. If the input date is a Thursday, it will give the next Thursday.

Use <= in the conditional if you want to display the input Thursday when given a Thursday, rather than displaying the next Thursday.