Month Codes in formula

[rhudgins]

Why is the formula in C6 returning the value "9985"? There is only one entry in column AF on the All Closed Positions tab with a January date in it so it should return the value of "1". In cell C7 the formula formula is working correctly for February. Is something wrong with the month code of "1" for January.

[shg]

Because Month(0) = 1

[Fotis1991]

Hi rhudgins.

I noticed that there are some macros. I think that the problem is there.....

I noticed that in AF column, even if is empty, the formula =month(af20)>(for example),gives 1 !!!!!

So your formula regognize the empty cells as Jaunury!!!!

I greated a helper column(AG) and put the formula =IF(AF20="";"";MONTH(AF20))

Then in Monthly Pnl Sheet, used this formula. =SUMIF('All Closed Positions'!AG14:AG10000;A17)

And works...!

Hope to helps you.

[Fotis1991]

....So simple Shg!!!!!!!!

I spend more than twenty minutes on this.........!

So not the macros......

Keep teaching us!!

[Haseeb Avarakkan]

Use TEXT function like,

=SUMPRODUCT(--(TEXT('All Closed Positions'!$AF$14:$AF$10000,"m;;")=A6&""))

So TEXT will format all the blank cells as "". Since you are on XL2010 you can use COUNTIFS for multi-conditional counting, SUMIFS for multi-conditional summing

[rhudgins]

But why is my formula recognizing the empty cells as January? I would rather not have to create a helper column so is there a way to get the formula to stop recognizing empty cells as January?

Shg- I am not sure what you mean by Month(0) = 1? What change should I make?

[Haseeb Avarakkan]

See DonkeyOte's explanation on this Thread

[rhudgins]

Thanks. With that same logic how can I convert the formulas in cells D26, D29 and D34.

Previosuly I used this sheet for 3/2011-12/2011. Now that I need to include January and it is not working, I am going to have to convert all of my old formulas. Ughhh

[Haseeb Avarakkan]

See the post #5.