CODEWITHSUNDEEP
excelexcel-formula
DillonCarter
DillonCarter

Reputation: 97

WEEKDAY Error With Excel

I have dates within a cell using this format: (February 13 2014).

When I try to use "WEEKDAY(C2)" I get #VALUE!

I have no idea how to fix this and I need some help to finish this web scraping project. Any ideas?

Upvotes: 1

Views: 2819

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60344

The problem is that the date is a string, and not a real Excel date formatted as you show. The WEEKDAY function needs have a "real Excel date" as its argument, not a string.

If the apparent spaces between the date components are spaces, then the following formula should work:

=WEEKDAY(--SUBSTITUTE(C5," ",", ",2))

EDIT: As David Zemens just pointed out, the double unary seems to be unnecessary with the weekday function. So

=WEEKDAY(SUBSTITUTE(C5," ",", ",2))

should be a better solution.

If they are something else, the formula would be different, but the principal would be similar

Another method: You may be able to convert it to a "real date" by using the Text to columns wizard, but DON't split it on anything. (You can do that by selecting something like TAB as the delimiter; since there are not tabs, no splitting). When you get to step 3, merely check that it is a date in MDY format. That wizard is pretty smart. Then you can use the WEEKDAY function directly.

Upvotes: 3

pnuts
pnuts

Reputation: 59485

Formula only: 

=WEEKDAY(DATE(RIGHT(C2,4),MONTH(LEFT(C2,FIND(" ",C2)-1)&1),(MID(C2,FIND(" ",C2)+1,2))))

with thanks to @tgeery.

Upvotes: 1

S. Miller
S. Miller

Reputation: 409

Your dates are not in the correct format.

WEEKDAY(DATE(2014,2,13))

should work because the argument for the weekday function needs to come from the DATE function.

Upvotes: -2

tgeery
tgeery

Reputation: 174

One way to do so... maybe not best since it involves adding so many rows

Add a fresh column D and E if not available..

Then use Text to Columns on Column C, with space as delimiter... (This will split column on spaces into 3 columns)

Add a new Column D with formula 

=MONTH(C3&1)

Make sure new Column D is of type General or Number rather than Date

Then you can use the following formula

=DATE(F3,D3,E3)

Upvotes: 1

Related Questions