We have Excel 2007 (German) and vista ultimate. I am trying Excel by building a roster but have run into a problem with the following formula.
I have the formula loaded into the yellow area cells BO20:BP21 on the Januar page. To test the formula, the months can be altered by changing the date in J7 cell on the “Interface” page.
As best as I can tell the formula doesn't work because the A8 row is formatted for dates and displays monthly day numbers, e.g. 1, 2, 3, 4,...-...29, 30, 31, 1, 2, 3, 4,...etc.
I tried putting =AT8 and =AU8 in two blank cells that are formatted as General. The cells return 40267 and 40268 respectively which (I think) is the number of days from Jan 1904 through 31 March 2014. This means each consecutive cell will simply be +1 and my formula wont work.
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.
So does anybody know how I can get around the Date formatting problem so I can perform calculations with the visible cell values.
Any ideas or suggested solutions are appreciated.
P.s. I cant see a way to upload an example, have I overlooked something?
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.
Thanks for the answers, I am new to excel so it may tak a bit of time to work through your responses :-)
I have updated my post to include a dropbox link.
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.