CODEWITHSUNDEEP
excelexcel-formula
Bjorn Morrhaye
Bjorn Morrhaye

Reputation: 731

Excel Increment index by 1 for each new week

i have a calendar document which I use and in this document I have an index value which should add 1 for each new week. So week 1 has value 1, week 53 has value 53, then week 1 of the new year should have value 54. Any idea how i can achieve this ? I have dates available, as well as week of year as a value.

I solved it for the first year by using this formula but it's giving me trouble for the next years as it doesn't take into account the short weeks at the end of the year.

Preview image

Here you see the problem: Week 1 of 2018 should have an index value of 107

trouble

Upvotes: 1

Views: 393

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

Maybe start your count with 1 and then, starting from row 2, use the following formula:

=IF(OR(YEAR(A2)>YEAR(A1),WEEKDAY(A2)=1),B1+1,B1)

This should return the following results:

enter image description here

Upvotes: 2

Related Questions