How to Extracting dates and days between 2 dates.

[Rick Rothstein]

Give this macro a try...

Sub FillInStartEndDates()
  Dim BeginDate As Date, StopDate As Date, NumberOfDays As Long
  Const StartDateCell As String = "C9"
  BeginDate = Range("B3").Value
  StopDate = Range("C3").Value
  NumberOfDays = StopDate - BeginDate + 1
  Range(StartDateCell).Resize(2).EntireRow.Clear
  With Range(StartDateCell)
    .Offset(1).Value = BeginDate
    .Offset(1).NumberFormat = "d-mmm"
    .Offset(1).AutoFill Destination:=.Offset(1).Resize(, NumberOfDays), Type:=xlFillDefault
    With 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

[Rajesh Kr Joshi]

Hi Rick,

This is awesome. Worked great.

Thanks for your help
Rajesh

[Rick Rothstein]

Hi Rick,

This is awesome. Worked great.

Thanks for your help

You are quite welcome, I am glad that I was able to be of some help to you.

[Rajesh Kr Joshi]

Hi Rick,

This code is working fine, excpet if I delete the dates in B3 or C3 (this is required to input fresh dates). Its give me run time 1004, Application defined or object defined error and highlights .Offset(1).AutoFill Destination:=.Offset(1).Resize(, NumberOfDays), Type:=xlFillDefault line.

Thanks
Rajesh

[Rick Rothstein]

Hi Rick,

This code is working fine, excpet if I delete the dates in B3 or C3 (this is required to input fresh dates). Its give me run time 1004, Application defined or object defined error and highlights .Offset(1).AutoFill Destination:=.Offset(1).Resize(, NumberOfDays), Type:=xlFillDefault line.

I'm confused. The code I posted is a macro which means it does not run unless you deliberately run it. Why would you run it if you do not have a date in both B3 and C3? Given the purpose of the code, what output would you expect from it if you don't have two dates in those cells?

[Rajesh Kr Joshi]

Hi Rick,

Thanks, I got the point, i will add button to run it.

Thanks
Rajesh

[Rajesh Kr Joshi]

Hi Rick,

Thanks, I got the point, i will add button to run it.

Thanks
Rajesh

Jut one thing, is it possible to shift and date plotter to sheet2? I mean insted of C9 , Target is sheet2 C9.