How to Extracting dates and days between 2 dates.

[Rajesh Kr Joshi]

I think this code will do what you want...

Sub FillInStartEndDates()
  Dim BeginDate As Date, StopDate As Date, NumberOfDays As Long, WS1 As Worksheet, WS2 As Worksheet
  Const StartDateCell As String = "C9"
  Set WS1 = Worksheets("Sheet1")
  Set WS2 = Worksheets("Sheet2")
  BeginDate = WS1.Range("B3").Value
  StopDate = WS1.Range("C3").Value
  NumberOfDays = StopDate - BeginDate + 1
  WS2.Range(StartDateCell).Resize(2).EntireRow.Clear
  With WS2.Range(StartDateCell)
    .Offset(1).Value = BeginDate
    .Offset(1).NumberFormat = "d-mmm"
    .Offset(1).AutoFill Destination:=.Offset(1).Resize(, NumberOfDays), Type:=xlFillDefault
    With WS2.Range(StartDateCell).Resize(, NumberOfDays)
      .Interior.Color = 10147522
      .Offset(1).Interior.Color = 15261110
      .FormulaR1C1 = "=TEXT(R[1]C,""ddd"")"
      .Resize(2).HorizontalAlignment = xlCenter
    End With
  End With
End Sub

Note that the code has variables for the source sheet (WS1) and the target sheet (WS2) which are set to Sheet1 and Sheet2 now, but can be changed if need be in the future.

Thanks Rick, as usual Perfect ..Working fine