I'm trying to get the odd numbered month (Jan[1], March[3], etc) and
display the next tenth of that month.

For example: If its the 9th of Jan, display 1/10/current year
1/10 display 1/10
1/11 display 3/10
2/1 display 3/10

11/9 display 11/10
11/10 display 11/10
11/11 display 1/10/next year

I'm almost there, but I can't get the syntax correct for the month
number. It works fine prior to the 10th, but after the 10th it
displays the current month during an odd month. I know I'm missing an
IF statement.

=DATE(IF(TODAY()DATE(YEAR(TODAY()),11,10),YEAR(TO DAY())+1,YEAR(TODAY())),IF(TODAY()DATE(YEAR(TODAY ()),11,10),1,ODD(MONTH(TODAY()))),10)

Am I just making this more confusing than it has to be?
I know the last false statement is the trouble "ODD(MONTH(TODAY()))",
but I can't get my head around it for some reason.

TIA

With dates in Col A enter this in row 1 of any col and copy down
=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)<=10,IF(MOD(MON TH(A1),2)=1,0,2),IF(MOD(MONTH(A1),2)=1,2,1)),10)

How it works;
IF(DAY(A1)<=10,IF(MOD(MONTH(A1),2)=1,0,2),IF(MOD(M ONTH(A1),2)=1,2,1)),

The above formula returns 0 if date is less or equal to 10 AND month is odd...
It returns 2 if date is 10 and month is Odd (Jan to Mar, Mar to May) and 1
if month is even (Fen to Mar...)

When you add this number to the month in =Date(year(a1),month(a1),10) you
get what you want.

"Greg L" wrote:

I'm trying to get the odd numbered month (Jan[1], March[3], etc) and
display the next tenth of that month.

For example: If its the 9th of Jan, display 1/10/current year
1/10 display 1/10
1/11 display 3/10
2/1 display 3/10

11/9 display 11/10
11/10 display 11/10
11/11 display 1/10/next year

I'm almost there, but I can't get the syntax correct for the month
number. It works fine prior to the 10th, but after the 10th it
displays the current month during an odd month. I know I'm missing an
IF statement.

=DATE(IF(TODAY()DATE(YEAR(TODAY()),11,10),YEAR(TO DAY())+1,YEAR(TODAY())),IF(TODAY()DATE(YEAR(TODAY ()),11,10),1,ODD(MONTH(TODAY()))),10)

Am I just making this more confusing than it has to be?
I know the last false statement is the trouble "ODD(MONTH(TODAY()))",
but I can't get my head around it for some reason.

TIA

With your help I finally got it. Thanks!
Here is what I ended up with:

=DATE(YEAR(TODAY()),(MONTH(TODAY())+IF(DAY(TODAY() )10,IF(MOD(MONTH(TODAY()),2)=1,2,1),IF(MOD(MONTH( TODAY()),2)=1,0,1))),10)

On Sun, 28 Jun 2009 23:11:01 -0700, Sheeloo
wrote:

With dates in Col A enter this in row 1 of any col and copy down
=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)<=10,IF(MOD(MO NTH(A1),2)=1,0,2),IF(MOD(MONTH(A1),2)=1,2,1)),10)

How it works;
IF(DAY(A1)<=10,IF(MOD(MONTH(A1),2)=1,0,2),IF(MOD( MONTH(A1),2)=1,2,1)),

The above formula returns 0 if date is less or equal to 10 AND month is odd...
It returns 2 if date is 10 and month is Odd (Jan to Mar, Mar to May) and 1
if month is even (Fen to Mar...)

When you add this number to the month in =Date(year(a1),month(a1),10) you
get what you want.

"Greg L" wrote:

I'm trying to get the odd numbered month (Jan[1], March[3], etc) and
display the next tenth of that month.

For example: If its the 9th of Jan, display 1/10/current year
1/10 display 1/10
1/11 display 3/10
2/1 display 3/10

11/9 display 11/10
11/10 display 11/10
11/11 display 1/10/next year

I'm almost there, but I can't get the syntax correct for the month
number. It works fine prior to the 10th, but after the 10th it
displays the current month during an odd month. I know I'm missing an
IF statement.

=DATE(IF(TODAY()DATE(YEAR(TODAY()),11,10),YEAR(TO DAY())+1,YEAR(TODAY())),IF(TODAY()DATE(YEAR(TODAY ()),11,10),1,ODD(MONTH(TODAY()))),10)

Am I just making this more confusing than it has to be?
I know the last false statement is the trouble "ODD(MONTH(TODAY()))",
but I can't get my head around it for some reason.

TIA

Thanks for your feedback. I am happy that you found the mistake in my formula
and corrected it...

"Greg L" wrote:

With your help I finally got it. Thanks!
Here is what I ended up with:

=DATE(YEAR(TODAY()),(MONTH(TODAY())+IF(DAY(TODAY() )10,IF(MOD(MONTH(TODAY()),2)=1,2,1),IF(MOD(MONTH( TODAY()),2)=1,0,1))),10)