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

[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]

ADD DATES.xlsmHi 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

[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