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.
|