Sunday, May 23, 2010

Calculate the Next Fixed Weekday (Tuesday for example)

How would you calculate the next Tuesday regardless today's weekday?

The idea is to reduce today to get always the same weekday (Sunday or 'zero' day for example) and add the target week day. this requires adjustment in case you already passed the day for this week and need to add another 7 days.

So, if you expect to get the next Tuesday while today is Tuesday the formula can look like this:

=A1-WEEKDAY(A1)+3+7-IF(WEEKDAY(A1)<3,7,0)

A1 is of course the reference date to add to.

No comments:

Post a Comment

Enter your comment here