Unable to identify current week in excel as Previous, Current or Next week

Question

I am trying to identify current week in excel. Tried weekday weeknum with different combinations but I am always getting somewhere or the other.

Assuming I have dates filled in range A1:A15 from 21-Nov-18 to 5-Dec-18. In column B after every corresponding cell, I wish to have two values "PW" "CW" and "NW" which stands for Previous week, Current week and Next week. How to use the weekday or any other formula to show the status in column B after every date from column A?

I think what I am going in the right direction but getting stuck somewhere where I am unable to figure out what would be the syntax to achieve desired result. PLease help

Desired solution is: 21-Nov to 25 Nov should have PW as status 26-Nov to 2-Dec should have CW as status 3-Dec to 5-Dec should have NW as status My week starts on Monday

Answer 1

Actually, you want to display 3 values: "PW", "CW" or "NW"

I split the answer in two formulas, which can be combined in a single one, but this way is more clear:

B1: =WEEKNUM(A1,2)-WEEKNUM(TODAY())

C1: =IF(B1<0,"PW",IF(B1=0,"CW","NW"))

Column B has to be in "General" or "Number" format, but you can simply hide it.

Screen capture in Excel

Answer 2

Use:

=IFERROR(CHOOSE((WEEKNUM(A1,2)-WEEKNUM(TODAY(),2)+2),"PW","CW","NW"),"")