Counting weeknumber

**sanlen** · 09-29-2010, 10:03 PM

Hi,

I am trying to convert a date into week number by using WEEKNUM function. I would like Monday is the first day of week and Sunday is the last day of the week.

i.e: 7-Sep-2010 (Monday) to 3-Oct-2010 (Sunday) should be WEEK39 for me, but result i got for 3-Oct-2010 (Sunday) is WEEK40.

I have attached a weekbook along. Please kindly advice

**jeffreybrown** · 09-29-2010, 10:09 PM

Did you look in Excel help for assistance.

WEEKNUM(serial_num,return_type)

Return_type Week Begins
1 Week begins on Sunday. Weekdays are numbered 1 through 7.
2 Week begins on Monday. Weekdays are numbered 1 through 7.

Try =WEEKNUM(A3,2)

**sanlen** · 09-29-2010, 10:51 PM

Hi,

Tested and working as what i wanted now. Thanks you very much.

I am trying to add this into IF function, but it seems does not work for me either.

=IF(ISNUMBER(B5),"Completed",IF(AND(WEEKNUM(A5,2)-1=WEEKNUM(TODAY(),2)-1, ISNUMBER(B5)=0),"done this week","not this week"))

What i am trying is that if Actual equal to any value then it would display "COMPLETED", but if any of them in for Focus is expect to finish sometime within current week then i would display "done this week", else not this week.

Please advice.

**jeffreybrown** · 09-29-2010, 11:04 PM

See if this will work for you in C3

=IF(ISNUMBER(B3),"Completed",IF(A3-$G$1<8,"done this week","not this week"))

In cell G1 I entered =Today() because this will prevent each formula from calculating Today. Instead the formula wll look for today in one place.

If you want Today() as part of the formula then...

=IF(ISNUMBER(B3),"Completed",IF(A3-TODAY()<8,"done this week","not this week"))

**sanlen** · 09-29-2010, 11:29 PM

Hi,

Thanks!

Working for me now, and i preferred to go with te second one.

However, i realized that i need to have one more condition.

If the focus date is reached and project still cannot be closed, and i then want to display as "over due".