Semi-month number

[MAP]

I am an Excel novice still using the simple excel 2003.

While I know how to get the week number, I want to now learn how to get the "semi-month" number using a formula.

For example A1 has the date, B1 would have formula to calculate the semi-monthly number
01 Jan = 01
15 Jan = 01
16 Jan = 02
13 Feb = 03
28 Feb = 04
10 Mar =05
25 Mar =06
07 May =09
17 May =10
16 July =14
31 July =14

 

[MAP]

My suggestion will be:

=(MONTH(A1)*2)+LOOKUP(DAY(A1)-(DAY(EOMONTH(A1,0))/2),{-16,0},{-1\0})

Thank you, Flashbond... but since I am using an old, old, old version of Excel, the EOMONTH function is not available.

 

[Legacy 143009]

This may work:

=(MONTH(A1)*2)+LOOKUP(DAY(A1)-(DAY(DATE(YEAR(A1),MONTH(A1)+1,0))/2),{-16,0},{-1,0})

 

[Rick Rothstein]

Mr. Rick Rothstein, I thank you again for your assistance. The initial formula you provided has worked PERFECTLY (the revised one failed with the 15 Feb date). The 01 Feb to 15 Feb is semi-month number 3, and 16 Feb to end of Feb is semi-month number 4.

You say my first formula worked "perfectly", so I am guessing it "answered" your question; however, you did not mark my message with that formula as the "Answer", instead you marked Flashbond's post telling me that that formula might not correctly handle February 15th as the "Answer". I'm guessing that was a mistake which you might want to correct.

 

[MAP]

You say my first formula worked "perfectly", so I am guessing it "answered" your question; however, you did not mark my message with that formula as the "Answer", instead you marked Flashbond's post telling me that that formula might not correctly handle February 15th as the "Answer". I'm guessing that was a mistake which you might want to correct.

I offer my apologies. I intended to click the check mark to express my satisfaction with Mr. Rothstein's initial formula [ =2*MONTH(B1)-(DAY(B1)<16) ] that worked perfectly. I am not sure what I may have inadvertently done, but I offer my humble appreciation to Rick Rothstein for his solution that solved my problem.