CODEWITHSUNDEEP
exceldateexcel-formula
Ole Henrik Skogstrøm
Ole Henrik Skogstrøm

Reputation: 6493

Dealing with overlaping week and year numbers in Excel

A new year with new problems! I have several Excel based rapports that pull data from an Oracle table. However i noticed a problem related to year and weeks in Excel. Here is and example:

Wrong result

The formula i use now is Week(A1;21) for the week and Year(A1) for the year.

Is there a way that i can get the correct year for the whole week based on the date in excel (see week 1 of 2015 in the picture above)

The result i want is this:

Correct result

Upvotes: 0

Views: 115

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7894

This is a possible solution:

=YEAR(A1)+AND(WEEKNUM(A1;21)=1;MONTH(A1)=12)

Upvotes: 1

Related Questions