Using WEEKDAY with INDIRECT returns wrong result

[Maximus Tatius]

Sorry folks, I've searched and searched and spent many hours trying to fathom this one out before posting here, but I need help!

I use the WEEKDAY function to return the day of the week (weekending Sunday... so e.g. my formula to return the weekday for a day in cell A1 would be:

=WEEKDAY(A1,2)

Naturally that works just fine, but sometimes I need to refer to dates in earlier rows and I've had trouble before with formulas screwing up if I insert new rows at a later date, so I was trying to use the following formula to reference a cell relative to the current one.

In B2 I would get a value from cell A1 using:

=INDIRECT(ADDRESS(ROW()-1,COLUMN()-1))

I then tried to get the WEEKDAY of the date in that cell using:

=WEEKDAY((INDIRECT(ADDRESS(ROW()-1,COLUMN()-1),2)))

but this returns the wrong result, e.g. it returns a "6" instead of a "5" for Friday 23rd May 2014.

Can anyone tell me where I'm going wrong?

Here's a link to a spreadsheet demonstrating this anomaly

I use Excel 2003. The formulae in rows 1 & 2 are where I was taking this a step further to return the actual Week Ending (Sunday) date, which should be the 25th May, not the 24th May.

Incorrect results returned are shown in the pink cells.

 

[Andrew Poulsom]

You have a misplaced parenthesis:

=WEEKDAY(INDIRECT(ADDRESS(ROW()-1,COLUMN()-1)),2)

Use the Evaluate Formula tool to see what your formula is doing.

 

[Maximus Tatius]

Wow! Thanks Andrew!

Yes, I'd been trying to use evaluate earlier but still couldn't quite figure out what was happening.

Thanks again!