WEEKDAY() and ADDRESS()

  • Thread starter Thread starter Jade
  • Start date Start date
J

Jade

Hello,

I have a spreadsheet which has many dates on it and I need a
complicated formula to look them up I use the address() function to
convert it to a format weekday() will recognise, but it gives me a
#VALUE! error for no apparent reason.

Try putting a date in A1, and "=WEEKDAY(ADDRESS(1,1))" in any other
cell.. why the error? what's the workaround?

Thanks!
 
Hello,

I have a spreadsheet which has many dates on it and I need a
complicated formula to look them up I use the address() function to
convert it to a format weekday() will recognise, but it gives me a
#VALUE! error for no apparent reason.

Try putting a date in A1, and "=WEEKDAY(ADDRESS(1,1))" in any other
cell.. why the error? what's the workaround?

Thanks!
Click to expand...

You don't understand the ADDRESS function.

The ADDRESS function "Creates a cell address as text".

So, if you used the Evaluate Formula Tool, you would see

WEEKDAY(ADDRESS(1,1))
WEEKDAY("$A$1")
#VALUE!


The WEEKDAY function requires a DATE as its argument. You are giving at string
which bears no resemblance to a date.

If what you want is the contents of A1 to be used as the argument for the
WEEKDAY function, then you could try =WEEKDAY(INDIRECT(ADDRESS(1,1)))

Again, this will only work if A1 contains a true Excel date, and not a string.
--ron
 
I have a spreadsheet which has many dates on it and I need a
complicated formula to look them up I use the address() function to
convert it to a format weekday() will recognise, but it gives me a
#VALUE! error for no apparent reason.

Try putting a date in A1, and "=WEEKDAY(ADDRESS(1,1))" in any other
cell.. why the error? what's the workaround?
Click to expand...

The return value from the ADDRESS function is the **text** for the cell
address, not the cell address itself. Why are you trying to do it the way
you posted? What is wrong with just specifying the address directly?

=WEEKDAY(A1)

Rick
 
Back
Top