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

 

[Dave Patton]

My first thought is a Lookup.
With B4, I named the Lookup information.

T202302.xlsm
	A	B
1
2	1-Jan-23	1
3	31-Jul-23	14
4	5-May-23	9
5
1a

Cell Formulas
Range	Formula
B2:B3	B2	=LOOKUP(A2,$D$2:$E$25)
B4	B4	=LOOKUP(A4,aL)

 

[Rick Rothstein]

Give this formula a try...

=2*MONTH(B1)-(DAY(B1)<16)

By the way, your original message says you are using XL2003 but your profile says you are using XL2007.

 

[Legacy 143009]

Give this formula a try...

=2*MONTH(B1)-(DAY(B1)<16)

By the way, your original message says you are using XL2003 but your profile says you are using XL2007.

Hi! Februray 15 may not play well with this formula.

 

[Rick Rothstein]

Hi! Februray 15 may not play well with this formula.

I never heard of a semi-month before so I thought it used the 15th/16th as the dividing line. I'm guessing that is not correct. From your comment, I'm guessing it splits the month in half, rounding fractions down. If that is correct, then February is the only month I would have wrong. This modified formula should work then...

=2*MONTH(B4)-(DAY(B4)<16)+(B4=DATE(YEAR(B8),2,15))

 

[Legacy 143009]

My suggestion will be:

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

 

[Legacy 143009]

I never heard of a semi-month before so I thought it used the 15th/16th as the dividing line. I'm guessing that is not correct. From your comment, I'm guessing it splits the month in half, rounding fractions down. If that is correct, then February is the only month I would have wrong. This modified formula should work then...

=2*MONTH(B4)-(DAY(B4)<16)+(B4=DATE(YEAR(B8),2,15))

Sorry for my prejudgement. I just thought like that. OP doesn't provide detailed information about this.

Fixing regional setting in my formula:

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

 

[MAP]

Hi! Februray 15 may not play well with this formula.

My first thought is a Lookup.
With B4, I named the Lookup information.

T202302.xlsm
	A	B
1
2	1-Jan-23	1
3	31-Jul-23	14
4	5-May-23	9
5
1a

Cell Formulas
Range	Formula
B2:B3	B2	=LOOKUP(A2,$D$2:$E$25)
B4	B4	=LOOKUP(A4,aL)

Dave Patton, I thank you for your suggestion. Of course, I was hoping to have things "automated" by Excel with a formula so I would not need to set up a list and use a LOOKUP function.

 

[Dave Patton]

I was hoping to have things "automated" by Excel with a formula

See Rick Rothstein's solution post #3.

 

[MAP]

Give this formula a try...

=2*MONTH(B1)-(DAY(B1)<16)

By the way, your original message says you are using XL2003 but your profile says you are using XL2007.

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.