Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Code To Add Date And Seven Day Week Number From Start Of Year

  1. #1
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12

    Code To Add Date And Seven Day Week Number From Start Of Year

    I need a code that when i place a date in a cell D10 (Example:25-January-2013) it will then add 40 days of dates daily to AP10.In D9 can it also add the weeknumber (every 7 days the weeknumber increases by 1) corresponding to the day date in D10 (iso).Can this be attached to a button.Enter the date in D10 then press the button and the dates auto insert across the sheet daily to AP10.
    Can the button say ADD DATE or REMOVE DATES.First date in D10.When the button says REMOVE dates all dates deleted when button pressed and cell D10 then says "add date here".If no date is placed in D10 and ADD DATE button is pressed a warning messagebox appears and says NEED TO ADD DATE .Will not work until date entered.When date entered in D10 "Add Date Here" disappears until REMOVE Date button is selected and again "Add Date Here" is displayed....not sure if this is possible but would be good if achieved

    CAn some one supply a code for this please

    Much appreciated

    Rich
    EXcel 2010

  2. #2
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    ADD DATES.xlsx

    I attached a file hopefully to show what i need.To insert dates and corresponding weeknumbers and removing the dates

    Much appreciated

    Rich
    Last edited by rich_cirillo; 12-15-2012 at 12:18 PM.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi rich_cirillo,

    Welcome to ExcelFox!!!

    Do you really need the code ?

    Enter the date in D10. In E10 and copied across

    =D10+7

    In D11 and copied across

    =WEEKNUM(D10)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Hi Admin

    Thank you for reply

    Yes i was hoping for a code to display
    1. In cell D10 "ADD DATE HERE" displayed when all dates removed by selecting Button
    2. Weeknumber to display weeknumber every 7th day (eg..19-1-2013 displays weeknumber 4 then the next weeknumber displayed is 5 for 26/1/2013..no weeknumbers displayed between 4 and 5 etc,,)
    3. Place date in D10 and select Button to then auto insert dates and weeknumbers

    thanks for your help

    Rich

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Put these subs in a standard module.

    Code:
    Const DateRange     As String = "D10:AP10"
        
    Sub AddDates()
        
        Dim dt  As Date
        
        If IsDate(Range("d10").Value) Then
            dt = Range("d10").Value
            With Range(DateRange)
                .FormulaR1C1 = "=rc[-1]+1"
                .Cells(1) = dt
                .Offset(-1).FormulaR1C1 = "=IF(MOD(COLUMNS(RC4:RC)-1,7)+1=1,WEEKNUM(R[1]C),"""")"
                .Offset(-1).Resize(2) = .Offset(-1).Resize(2).Value
            End With
        End If
        
    End Sub
    Sub ClearDates()
        
        Range(DateRange).Offset(-1).Resize(2).ClearContents
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Hi

    Thank you .Works really well

    1. When selecting Delete Dates can a "ENTER DATE HERE" be displayed in D10 after Dates deleted....when dates added it disappears
    2. Can a Button be used.Button displays "ADD DATES" when no dates in D10...Button displays "REMOVE DATES" after dates have been entered

    Appreciated

    ADD DATES.xlsm

    Rich

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    add this line in ClearDates sub

    Code:
    range(daterange).cells(1)="ENTER DATE HERE"
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Thank you and appreciated

  9. #9
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    It now enters the "ENTER DATE HERE" in D10...next i place the date in D10 and run ADD DATES....it just removes the Date and place "ENTER DATE HERE" .. no dates added.

    Thanks

  10. #10
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    Read again post 7 from Admin.
    Put this
    Code:
    range(daterange).cells(1)="ENTER DATE HERE"
    in ClearDates

Similar Threads

  1. Nth Day Of Week Number Using Formula
    By paul_pearson in forum Excel Help
    Replies: 2
    Last Post: 06-16-2013, 06:40 PM
  2. Data Validation For Selecting Date And Week Number
    By paul_pearson in forum Excel Help
    Replies: 8
    Last Post: 06-16-2013, 05:07 AM
  3. Replies: 5
    Last Post: 06-15-2013, 12:40 PM
  4. Date Format From Start Day To End Day
    By PcMax in forum Excel Help
    Replies: 2
    Last Post: 03-10-2013, 02:07 PM
  5. Week Number And Week Start Day of Week
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 10-24-2011, 07:33 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
  •