CODEWITHSUNDEEP
excelexcel-formula
aab
aab

Reputation: 1739

Calculate the start date and end date of week number

I'm using the following formulas to obtain the start date and end date of the week number, given the week number and the year:

Start of week:=MAX(DATE(A2,1,1),DATE(A2,1,1)-WEEKDAY(DATE(A2,1,1),2)+(B2-1)*7+1)

End of week: =MIN(DATE(A2+1,1,0),DATE(A2,1,1)-WEEKDAY(DATE(A2,1,1),2)+B2*7)

It seems to work well except that week 1 of 2019 begins on 31-12-2018 but my formula for the start date of the week 1 shows it as 01-01-2019. What is the problem with my formula?

enter image description here

Upvotes: 0

Views: 5362

Answers (2)

Zack
Zack

Reputation: 2341

As I noted in my comment, the easiest way is just to subtract 6 from your end date. If your data is in a table, your formula would be =[@EndDate]-6. Otherwise, it would be something like =E2-6.

Upvotes: 0

Zack E
Zack E

Reputation: 706

Just subtract 6 from the end date formula

=(MIN(DATE(A2+1,1,0),DATE(A2,1,1)-WEEKDAY(DATE(A2,1,1),2)+B2*7))-6

Upvotes: 0

Related Questions