Excel two column and condition-CodePudding

I have a column with dates like so where w represents weeks and d represents days.

Column1   Column2 
in 1w3d   out 1w5d
in 2w0d   out 3w0d
in 24w2d  out 23w0d
in 1w0d   out 1w2d

Basically what I want to do is to check if both dates in Column 1 and Column 2 are between 1 week and 24 weeks. Both column values must be within the range.

Thus the output would be

Column1   Column2   Column3 
in 1w3d   out 1w5d  Between 1 week and 24 weeks
in 2w0d   out 3w0d  Between 1 week and 24 weeks
in 24w2d  out 23w0d Not between
in 1w0d   out 1w2d  Not between

I have an if statement for this written in excel that is like so =IF(AND(A2>"in 1w0d,A2<"in 24w0d",B2>"out 1w0d,B2<"out 24w0d"),1,0) However this does not get the desired results

CodePudding user response：

Column3=MID(A2,SEARCH(" ",A2),SEARCH("w",A2)-SEARCH(" ",A2))*7 (MID(A2,LEN(A2)-1,1))

Column4=MID(B2,SEARCH(" ",B2);SEARCH("w",B2)-SEARCH(" ",B2))*7 (MID(B2,LEN(B2)-1,1))

Column5=IF(AND(C2>7,C2<24*7,D2>7,D2<24*7),1,0)

Maybe you need C2>=7 etc depending on where to draw the line