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

Attachment 505

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

Much appreciated

Rich

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)

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

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

---

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

Attachment 507

Rich

add this line in ClearDates sub

Code:

---

range(daterange).cells(1)="ENTER DATE HERE"

---

Thank you and appreciated

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

Read again post 7 from Admin.
Put this

Code:

---

range(daterange).cells(1)="ENTER DATE HERE"

---

in ClearDates

sorry my apologies...yes now wroks.Thank you

I`1m new to this so finally i need to use a button for these 2 operations..please how do i assign a button to achieve the result from my previous request in post #6

thank you

Please see attached file.....the week number is 53 and it skips week 1...can this be altered please?

Try this instead...

Code:

---

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,ROUNDUP((R[1]C-DATE(YEAR(R[1]C),1,0))/7,0),"""")"
            .Offset(-1).Resize(2) = .Offset(-1).Resize(2).Value
        End With
    End If
   
End Sub

---

Hi Excel Fox

Thanks that works great with the buttons...the only issue is if i place 31-12-2012 it has the week number as 53 instead of 1

Thanks again

rich, i thought 31-12-2012 should be week number 53. How can it be 1?

Attachment 513Hi Excel fox

I am sure there is only 52 weeks in a year...please see attached

This formula gives 52 weeks using the same dates..started with 17/12/2012 =IF(WEEKNUM(D10,21)<>WEEKNUM(C10,21),WEEKNUM(D10,2 1),"")

Thanks

rich, there are 365 days in a year, and in a leap year, 366. If one were to assume that the very first day of the year started with the very first weekday, then by the time you reach the 364th day, you would have completed 52 weeks. From that logic, the next day (or two) should be the 53rd week. The question is, do you want to start showing the actual week number, or the index of each 7 day period that 'makes' a week starting from 1st of January?

Hi Excel Fox

Yes you are correct...my apology....Yes week starting from 1st of January (2013,2014,2015 etc..etc..) is what i want please

Thank you