WeekNum relative to a specific date?

[enrid98]

Does anyone know of a way to use the WEEKNUM function but to have it display the week number relative to a specific date? For example, If my column of dates starts at May 7th, where I would like May 7th to appear as Week "1" and have it continue from that point on where May 14 (for example) would be week 2, etc.

 

[Rastaman]

I don't know of a way to modify the output of the WEEKNUM function. However, you could construct a formula to do this.

Two variations, assumes dates are in column A, drag formulas down to cover all rows with dates:

=WEEKNUM(A1)-WEEKNUM($A$1)+1

=WEEKNUM(A1-WEEKDAY($A$1)+1)-WEEKNUM($A$1)+1

In the first formula Sunday is always the first day of the week. In the second formula the date entered in A1 defines the first day of the week.

Rick

 

[barry houdini]

This probably depends on what you want to happen after a year.....

If you have your start date in G2 then this formula will give you the weeknum for a date in A1

=INT((A1-$G$2)/7)+1

but the week numbers will just increment continually, even after a year has passed, so you can get week number 70 etc. This allows G2 to be any day.

Or if you want the number to return to 1 after a year, assuming G2 contains a Monday start date

=WEEKNUM(A1-(WEEKNUM(G$2,2)-1)*7,2)