week number not saturday or monday start(formula not macro)

[smakatura]

can weeknum work with the week starting on saturday instead of sunday or monday?

if not, what are my options?

 

[FormR]

There is probably a more elegant solution but you could try this:-

assumes your date is in A1

=WEEKNUM(A1)+(TEXT(A1,"DDD")="SAT")-(TEXT(DATE(YEAR(A1),1,1),"DD-DDD-MMM")="01-SAT-JAN")

 

[FormR]

A little cleaner:

=WEEKNUM(A1)+(TEXT(A1,"DDD")="SAT")-(TEXT(DATE(YEAR(A1),1,1),"DDD")="SAT")

 

[barry houdini]

can weeknum work with the week starting on saturday instead of sunday or monday?

It depends how you want to define the weeks, I think. With standard WEEKNUM function 1st Jan is always in week 1 and week starts on the first Sunday (or Monday) after that.

If you want the same sort of setup but with week 2 starting on first Saturday after that then try this formula

=INT((A2-DATE(YEAR(A2),1,1)-WEEKDAY(A2+1))/7)+2

 

[smakatura]

I used the int formula and it worked great