Date Shift Formula Requirement

Hi

If possible I’d like some assistance with the following please.

We have some data which contains dates in a 1st single column.

The result is to be in the 2nd column.

The requirement are:

- When the date is a weekday we don’t need to do anything, the result in the 2nd column is the same date.

- When the date is a Saturday or Sunday the result required is the date of the following Monday.

The following I hope describes the requirements:

Starting Date in 1st column

Resulting Date in 2nd column

13/07/09 13/07/09 Weekday dates remain unchanged but formula still in cells
14/07/09 14/07/09
15/07/09 15/07/09
16/07/09 16/07/09
17/07/09 17/07/09
18/07/09 20/07/09 Saturday's date is "jogged" to the following Monday
19/07/09 20/07/09 Sunday's date is "jogged" to the following Monday
20/07/09 20/07/09
21/07/09 21/07/09

So far my efforts don’t extend beyond a formula which is working just for a Saturday or a Sunday and these are returning an invalid result when the day is a weekday, I’d like a single formula to do this.

Please send your responses where possible.

Many thanks in advance.



Henry.

..............................................................
Henry Thompson
Resource Planning Officer, Programme Management Office
The National Strategies

One way:

B1: =A1+LOOKUP(WEEKDAY(A1,2),{1,6,7},{0,2,1})

Another way - making use of the Analysis ToolPak (activated via Tools - Addins)

B1: =WORKDAY(A1-1,1)

WORKDAY has the added advantage of being very flexible, and like NETWORKDAYs it has an optional argument for a range of public holidays to be excluded alongside the weekends.