Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Dates in the columns up to last date of the month

    Hello experts !
    I have a dates in the column C example 1/1/2013 to 4/1/2013 , I need to fill the date series till last date of the month. If it is February e.g 1/2/2013 to 7/2/2013 the column will fill till last date of month February.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Farrukh

    Do you want to do this manually or with vba????

    For manually, enter the first date required in a cell.
    Then select that cell.
    In the bottom-right-corner of the selected cell, you will see a small black square.
    Hold the mouse-button down on this small black square, and then 'drag' downwards as many rows as you like.
    each cell will increment by one day.

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Respected zeddy ,
    Actually the date is coming from data base in report template so currently I am doing manually after generating the report . If it will be VBA will easy life

    Thanks

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Farrukh

    ..the attached file shows a vba method to fill dates to the last day of the month, based on the last entry found in column [C]

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2013-08-28)

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    If it is February e.g 1/2/2013 to 7/2/2013 the column will fill till last date of month February
    farrukh

    If I read your post correctly, column C has a range of dates that you want trimmed to the last date of the current month. In the example, I have a range of dates from 6/1/2013 to 10/31/2013. Since the current month is August, the list will be trimmed from 6/1/2013 to 8/31/2013. I hope this is what you are looking for.

    Maud

    Code:
    Sub TrimList()
    'DECLARE AND SET VARIABLES
    Dim cell As Range
    Dim rng As Range
    LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    ActiveMonth = Month(Date)
    Set rng = Range(Cells(3, 3), Cells(LastRow, 3))
    '----------------------------------------------------
    'COMPARE DATES TO ACTIVE MONTH
    For Each cell In rng
    If IsDate(cell) Then  'IF VALID DATE
        If Month(cell) > ActiveMonth Then cell = ""  'TRUNCATE LIST
    End If
    Next cell
    
    End Sub
    Attached Files Attached Files

  7. #6
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Zeddy/Maudie

    The solution which is provided by Zeddy works for me .Thank you both for your kind help

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    If it is February e.g 1/2/2013 to 7/2/2013 the column will fill till last date of month February.
    Farrukh,

    According to your post, you are looking to fill the list of dates in a time range to the end of the current month. If you are satisfied with code that will take the last date in the list and fill to the end of that month, in this case to July 31, you were not very clear with your request.

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maudibe

    ..the problem is assuming 7/2/2013 is July i.e. July 2nd, and not 7th Feb.
    Farrukh did say "If it is February e.g. ".
    ..its always an issue with dates when dealing with international requests!

    zeddy

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Noted.

Posting Permissions

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