Results 1 to 10 of 10

Thread: How to Extracting dates and days between 2 dates.

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14

    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 advanceExtract Date and Arrange.xlsx
    Rajesh

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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

  3. #3
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Hi Rick,

    This is awesome. Worked great.

    Thanks for your help
    Rajesh

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajesh Kr Joshi View Post
    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.

  5. #5
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    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

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajesh Kr Joshi View Post
    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?

  7. #7
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Hi Rick,

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

    Thanks
    Rajesh

  8. #8
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Quote Originally Posted by Rajesh Kr Joshi View Post
    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.

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajesh Kr Joshi View Post
    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.

  10. #10
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Quote Originally Posted by Rick Rothstein View Post
    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

Similar Threads

  1. List of Dates In DropDown Starting From Today()
    By rich_cirillo in forum Excel Help
    Replies: 5
    Last Post: 04-10-2013, 05:58 PM
  2. Replies: 2
    Last Post: 02-23-2013, 09:18 PM
  3. Formula to Display Month and Dates Using Spin Button
    By ayazgreat in forum Excel Help
    Replies: 6
    Last Post: 11-21-2012, 10:19 PM
  4. The Number of Years, Months and Days Between Two Dates
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 7
    Last Post: 06-08-2012, 10:35 PM
  5. Visual Plotter basis given dates and activity
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 03-07-2012, 02:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •