2

https://www.dropbox.com/s/v6nd2z4icftmnvf/example1.xlsx

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.

=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

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.

Regards.

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.

1
  • You can use dropbox to upload your problematic file.
    – Kamil
    Aug 21 '14 at 16:52
0

Encapsulate your formula with the "TEXT" function. For example, =TEXT(YourFormulaHere,"yyyy/mm/dd") will return 2010/03/30. It will convert the generic number 40267 (your result as mentioned above) to a readable date format.

You can use any standard date formatting, just like you would in the cell format properties. Here's a link to see what your options are.

http://office.microsoft.com/en-ca/excel-help/format-a-date-the-way-you-want-HA102809474.aspx

0

To work with fields that are formatted as a Date type you can always use the Day() function:

So ammend your formulas to include =IF(DAY($AT$8)>DAY($AU$8)... and so on.

As an example the DAY function returns the day of the month, which I believe is what you are after, like so:

26/07/2014  |   26  (=DAY(A1))
27/07/2014  |   27  (=DAY(A2))
1
  • IT Worked...Wow, I have spent ages on this problem and the answer was so simple. Thankyou
    – bobsone1
    Aug 21 '14 at 17:28
0

You can use Nate Bergeron tip and it will work.

However I think you should use =DAY(), =MONTH(), =YEAR() etc. functions instead of formatting cells to display only month.

These functions return numbers when you use cell for something, full date is not stored in them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.