How to Extracting dates and days between 2 dates.

[Rajesh Kr Joshi]

Hi ,

I need to extract the dates and days basis start and end date. I wants to automate this using a macro code so that when the user inputs Start date (C2) and End date(C3) it auto extracts the date and day and plot it in the attached format.

Thanks in advanceExtract Date and Arrange.xlsx
Rajesh

[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.

[Rick Rothstein]

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

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.

[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