Excel Date Macro

You don't need a macro for this one, a simple formula will do. Excel allows date arithmetic involving dates and numbers (representing number of days). For example, subtracting two dates will return the number of days between them; adding a date and a number x will return a date that is x days away from the first one. The formula "=B3+1" in B5 (and so on down the line) should do the trick.

The big question is the part that you mentioned where the gap between days is dynamic. How exactly does this gap change? If it's a simple matter of deleting and inserting rows between days, Excel will automatically adjust the formulas so that the cell for Monday, wherever it may move, will still refer to Sunday + 1, and so on. If it's more complicated than that, you'll have to give more details. Hope this helps.

Hi Michael

Try this one. You'll need to enter the bottom row of data; it would be more complicated (but certainly possible given more information than I have) to detect the end of the data automatically.

Sub mcrDateFill()
Dim intRow As Integer
Dim intMaxRow As Integer
Dim intCol As Integer
Dim datWeekendDate As Date
Dim strTemp As String
Dim intDateMod As Integer

intDateMod = 0
intCol = 2
intMaxRow = CInt(InputBox("Enter bottom row", "Enter parameter"))
datWeekendDate = Range("B3").Value
For intRow = 5 To intMaxRow Step 1
Range(Cells(intRow, intCol), Cells(intRow, intCol)).Select
strTemp = ActiveCell.Value
Select Case strTemp
Case "Monday"
intDateMod = 1
Case "Tuesday"
intDateMod = 2
Case "Wednesday"
intDateMod = 3
Case "Thursday"
intDateMod = 4
Case "Friday" intDateMod = 5
Case Else
ActiveCell.Value = datWeekendDate + intDateMod
End Select
Next intRow
End Sub