Formula returns wrong value

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