How to Extracting dates and days between 2 dates.

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 advanceAttachment 302
Rajesh

Give this macro a try...

Code:

---

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

---

Hi Rick,

This is awesome. Worked great.

Thanks for your help
Rajesh

Quote:

---

Originally Posted by Rajesh Kr Joshi

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.

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

Quote:

---

Originally Posted by 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.

---

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?

Hi Rick,

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

Thanks
Rajesh

Quote:

---

Originally Posted by 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.

Quote:

---

Originally Posted by Rajesh Kr Joshi

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

Code:

---

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.

Quote:

---

Originally Posted by Rick Rothstein

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

Code:

---

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