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

[rich_cirillo]

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

[rich_cirillo]

505

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

Much appreciated

Rich

[Admin]

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)

[rich_cirillo]

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

[Admin]

Hi

Put these subs in a standard module.


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

[rich_cirillo]

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

507

Rich

[Admin]

add this line in ClearDates sub


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

[rich_cirillo]

Thank you and appreciated

[rich_cirillo]

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

[Ingolf]

Read again post 7 from Admin.
Put this


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


in ClearDates

[rich_cirillo]

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

[rich_cirillo]

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

[Excel Fox]

Try this instead...


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

[rich_cirillo]

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

[Excel Fox]

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

[rich_cirillo]

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,21),"")

Thanks

[Excel Fox]

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?

[rich_cirillo]

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