Formula returns wrong value

[bobsone1]

Hi
I have a problem with this formula;
=IF($AT$8>$AU$8;COLUMN(AT1)-COLUMN(P1);IF($AS$8>$AT$8;COLUMN(AS1)-COLUMN(P1);IF($AR$8>$AS$8;COLUMN(AR1)-COLUMN(P1);COLUMN(AQ1)-COLUMN(P1))))+1




When I test it in a blank work sheet and give the cells AP8:AV8 consecutive numbering (27, 28, 29, 30, 31, 1 and 2) it returns the value(s) (column counts) I am seeking i.e. 28, 29, 30 or 31 when the transition back to 1 is moved i.e. 27, 28, 1, 2, 3, 4, 5 returns 28 and 27, 28, 29, 1, 2, 3 4 returns 29 etc.
However, when used in my active work sheet (A8:... row cells are formatted as T; our Excel is German so I am guessing T = Tag/Day) it only ever returns 28, and when I try it in its intended time formatted cells ([h]:min;@) the formula returns 672:00, which I think is what it should be when Excel tries to format 28 as a time.
I have formatted the cells that way so the A8:... row formatting combined with the formula A8+1, B8+1 etc will give an accurate start/end point for the months of the year.

I am guessing my new formula is reacting to something already in the AQ8:AU8 cells (i.e. AQ8+1).


In short; the above formula works correctly in a blank (clean) work sheet but only returns 28 when in my active sheet which has cells formatted to count days of a month and a simple formula in the targeted AQ8:AU8 cells.


Does anyone have advice as to how I might go about identifying (and solving) the problem?


Regards.