Thread: Work out generic formula to deduce number of days to add to date to hit nearest Fri

Hi,
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
5-Mar-11 |Saturday|7|6|6
6-Mar-11 |Sunday|1 |5|6
7-Mar-11 |Monday|2|4|6
8-Mar-11 |Tuesday|3|3|6
9-Mar-11 |Wednesday|4|2|6
10-Mar-11 |Thursday|5|1|6
11-Mar-11 |Friday|6|0|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
=> 5
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

Originally Posted by bazzer

Hi,
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
5-Mar-11 |Saturday|7|6|6
6-Mar-11 |Sunday|1 |5|6
7-Mar-11 |Monday|2|4|6
8-Mar-11 |Tuesday|3|3|6
9-Mar-11 |Wednesday|4|2|6
10-Mar-11 |Thursday|5|1|6
11-Mar-11 |Friday|6|0|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
=> 5
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

Count your days like this and your formula will work:

Saturday = 1
Sunday = 2
Monday = 3
Tuesday = 4
Wednesday = 5
Thursday = 6
Friday = 7

Please keep in mind this is only for Friday. If you pick another target day, that day will be number 7, let's say the target was Tuesday, then you'd have to count your days like this:

Wednesday = 1
Thursday = 2
Friday = 3
Saturday = 4
Sunday = 5
Monday = 6
Tuesday = 7

The formula is right, you just HAVE to change the order of the days.