Thread: Calculate date following a specified weekday

Hi guys

I have been trying to work out a formula for what seems a simple problem, but to no avail. Would really appreciate any help, or even a point in the right direction.

Background
I have a list of data in Excel, over 500 lines in fact, and I need to determine a certain date based on a weekday which follows a different date.

For example:

Process Date Cycle Day Due Date
31/08/2011 Tuesday ??/??/????
31/08/2011 Friday ??/??/????
02/09/2011 Friday ??/??/????

The date I need to determine (the Due Date) is the Cycle Day which follows (or is on) the Process Date.

For instance, the correct dates for the above list would be:

Process Date Cycle Day Due Date
31/08/2011 Tuesday 06/09/2011
31/08/2011 Friday 02/09/2011
02/09/2011 Friday 02/09/2011

At the moment, I'm using a ludicrously complex date array and HLOOKUP, but this isn't suitable for the vast amount lines I have as each line has to be calculated individually. See the attached Excel file.

Is there any formula I could use in column C to determine the date I need?

Thanks

Paul

Can do this with a user defined function to keep the complexity down.

Open the VBA editor (Alt F11) and a new module (Insert - Module) and paste in the following.

Function DueDate(ProcessDate As Date, WeekDay As String) As Date
Dim WeekdayNumber As Integer
Select Case WeekDay
    Case Is = "Monday"
        WeekdayNumber = 1
    Case Is = "Tuesday"
        WeekdayNumber = 2
    Case Is = "Wednesday"
        WeekdayNumber = 3
    Case Is = "Thursday"
        WeekdayNumber = 4
    Case Is = "Friday"
        WeekdayNumber = 5
End Select

DueDate = ProcessDate
Do While WorksheetFunction.WeekDay(DueDate, 2) <> WeekdayNumber
    DueDate = DueDate + 1
Loop
End Function

Go back to the sheet (Alt F11) and type in to a cell ..

=duedate(A2,B2)

where the process date and week day are in columns A and B respectively. Copy down your 500 rows like any other formula.