+ Reply to Thread
Results 1 to 11 of 11

Add +7 Days to a column named Date

  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    Alabama
    MS-Off Ver
    2010 +
    Posts
    12

    Talking Add +7 Days to a column named Date

    Hello all,

    I am so glad to have this resource but I will get straight to the point. I had a worksheet that I have to go into and change the dates every week. so lets say the date was 10/10/14, I have to change it to 10/17/14 next week, then 10/24/14, then 10/31/14...11/7/14 and so on. I think you get the point by now. I do, however, have some rows in that column that are blank - so the function would have to skip empty columns and only change columns with the date in them. Please let me know if you need any other information about this.

    Thanks,

    Z

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Add +7 Days to a column named Date

    If you want to change dates on every Monday of the next week, You may try this workbook open event like this.....
    The following codes assumes that you have dates in col. A starting from A2 then the code will change the dates in col. A once you open the workbook on Monday.
    Please Login or Register  to view this content.
    Is this what you are trying to achieve?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Add +7 Days to a column named Date

    Assuming Column A is your date column
    Try this in any other corresponding cell
    [Formula]=if(Isnumber(A1),A1+7,"")[/Fomula]
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  4. #4
    Registered User
    Join Date
    07-31-2014
    Location
    Alabama
    MS-Off Ver
    2010 +
    Posts
    12

    Re: Add +7 Days to a column named Date

    I have the date as 11/20/14, Month/Day/Year and I want the Day XX to change to +7 of whatever it is. so 21, would be 28.

  5. #5
    Registered User
    Join Date
    07-31-2014
    Location
    Alabama
    MS-Off Ver
    2010 +
    Posts
    12

    Re: Add +7 Days to a column named Date

    It is in column D, and say row 2 or 3 are BLANK, but row 4 has 11/21/14 in it, row 5 is blank, row 6 has 11/22/14 in it, row 7 blank, row 8 has 11/23/14 in it. I want the days to go up 7 when I hit the macro button.

  6. #6
    Registered User
    Join Date
    07-31-2014
    Location
    Alabama
    MS-Off Ver
    2010 +
    Posts
    12

    Re: Add +7 Days to a column named Date

    When I put in Vikas function it leaves the cell blank

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Add +7 Days to a column named Date

    Try this......
    Please Login or Register  to view this content.

  8. #8
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Add +7 Days to a column named Date

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    is working for me...
    You have to change both cell references, and Moreover have to change the format to date as well...

  9. #9
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    122

    Re: Add +7 Days to a column named Date

    Hi zdsthilaire,

    Please try this code.......It's worked for me

    Sub Add7Days()
    Dim c As Integer, Lr As Long
    Lr = Sheets("sheet1").Range("d" & Rows.Count).End(xlUp).Row
    For c = 1 To Lr
    If IsDate(Sheets("Sheet1").Cells(c, 4)) Then
    'Cells(c, 4).Font.Bold = True
    Cells(c, 4) = DateAdd("d", 7, Cells(c, 4))
    End If
    Next c

    End Sub


    If Solved, Please close the thread.
    Please Click * Reputation, If Solved.

  10. #10
    Registered User
    Join Date
    07-31-2014
    Location
    Alabama
    MS-Off Ver
    2010 +
    Posts
    12

    Re: Add +7 Days to a column named Date

    I used sktneer's and it works great. I do appreciate all the help!

  11. #11
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Add +7 Days to a column named Date

    Glad I could help. Thanks for the feedback.
    If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 03-07-2014, 03:12 PM
  2. [SOLVED] Lookup Date 14 days prior and add multiple values for that date into column
    By Shylmysten in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-03-2013, 02:15 AM
  3. Add Date in Column A Plus Number of Days in Column B and show result in Column C
    By excelforumcrisis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2013, 02:01 PM
  4. [SOLVED] Search 1st Column for Date older than 5 days based on 2nd column contents
    By Nu2Java in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-31-2012, 10:49 PM
  5. Replies: 3
    Last Post: 08-22-2006, 04:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1