I am trying to work out a generic formula to deduce the number of days to add to any date to in turn deduce the date of the next Friday closest to that date, using the week day number.
So the given constants are:
Target week day number: 6 (Friday)
Number of Days in week: 7
Date | Day | Week Day Number | Number of Days to Add | Target Week Day Number
6-Mar-11 |Sunday|1 |5|6
So for example for the date 6-Mar-2011 which is a Sunday the following formula will work:
=> Target Week Day - WeekDay Number
=> 6 - 1
So if I add 5 days to 6-Mar-2011 I will hit the target Friday date of 11-Mar-2011
but this formula does not work for the first date listed in above table 5-Mar-2011.
So I was hoping to get advice on a single generic formula to achieve this and the approach to take on coming up with this formula