Formula returns wrong value

bobsone1 · #1 08-17-2014, 02:06 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to release hard returns?	markg2	Word	13	05-08-2023 01:38 PM
Selection.InlineShapes(1).ScaleHeight returns '0'	NobodysPerfect	Word VBA	1	06-29-2014 03:59 PM
IF formula returns wrong result	mashley	Excel	3	09-07-2012 07:03 AM
No tabbing, no returns in forms?	rdowney1	Word	7	06-29-2012 08:35 AM
IF formula returns TRUE instead of evaluating COUNTIF	ColinC	Excel	4	12-28-2011 08:21 AM