How to add week number based on the sheet summary date field?

sweta22

I am trying to add week number based on the sheet summary date.

For example, in the sheet summary date, I have added date i.e., 06/12/23

Then in the Week Number column, it should populate as week 1 till 06/18/23.

I have used the formula: =WEEKNUMBER(date#) - WEEKNUMBER(date#) + 1

Can anyone suggest how to do this?

Matt Johnson

Hi @sweta22

The WEEKNUMBER function returns the week number of the 52 weeks in a year. The week number for 6/12 is 24.

The formula you used above will always return the number 1. (X Value minus X Value plus 1 = 1).

So to acheive what you are describing, you'll need to build table with dates and week numbers in a new sheet. Then do a cross sheet index match formula in the week number column to pull in the week number based on the date. The WEEKNUMBER function won't come into play in this option.

I hope that helps.

Matt

sweta22

Hi @Matt Johnson ,

I didn't understand this. Actually, I want the week to begin on the date I've entered i.e., "Week 1" would be from June 12 to June 18, "Week 2" would be from June 19 to June 25, and so on.

Matt Johnson

Hi @sweta22

You could make a table in another sheet. Column1 should be dates from June 12th 2023 to however farout makes sense for this situation, maybe December 31st 2025. In Column2 list Week 1 for the first 7 days, Week 2 for the next, etc. That part is tedious but shouldn't take too long at all really. Then on the original sheet do an index match formula that looks at the date on your original sheet and compares it to the new date/week table you've created, and returns the Week number.

I hope that all makes sense. Thanks,

Matt

sweta22

@Matt Johnson Thanks a lot for this. This may work.