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.

Pecoflyer · #2 08-18-2014, 09:11 AM

Përhaps post a sample sheet showing what you have and the expected result?

bobsone1 · #3 08-19-2014, 12:12 AM

HI.

I tried a simple sheet, but I figured the actual workbook provides a better explanation :-)

So here is the formula in question.
=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

I have the formula loaded into the yellow area (Januar BO20:BP21)
I wanted to use this formula to give an actual count of each months days, irrespective of the months length and any start date loaded by the end-user(s)

When I test it in a blank work sheet and manually number the cells AP8:AV8 (27, 28, 29, 30, 31, 1 and 2) it returns the value(s) (column counts) I am seeking i.e. 27, 28, 1, 2, 3, 4, 5 returns 28 and 27, 28, 29, 1, 2, 3 4 returns 29 etc. However, when it is in my active workbook, the formula only ever returns 28.

My reason for attempting this:
I had intended to combine the formula with =Interface!$K$1*(VLOOKUP(C9;Lists!U7:V52;2;FALSE)* (31/1,4)) and situate the combined formula in the column BG9:BG35 (which is formatted [h]:min;@).
The idea being that the time value in Interface K1:L1 (representing one standard shift hours) and the two combined formula would form an equation to provide a “Required” months rostered hours for each person while accounting for their % of full time work, the actual number of Mon-Fri work days in a month multiplied by a time which represents one standard shift.

I hope this makes things a bit clearer.

Regards.

P.s. To test the formula, the months can be altered by changing the date in “Interface” J7.

Pp.s. I don't know if this matters, but because I needed negative time values I have changed the excel start date from Jan/1900 to Jan/1904.

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