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