Help with #value! error

[Madrizmoreno]

Hi, can someone help with this error in excel. My formula is IF(WEEKDAY(DATEVALUE(MoMonth&" 1, "&MoYear))=COLUMN(B$2),1,IF(LEN(A6)>0,A6+1,"")) but cannot manage to solve it by meself.

Its a week planner that adds the correct date and corresponding task to the day.

Any help is much appreciated. Thanks.

 

[jsotola]

you have not explained which error you get

what are MoMonth and MoYear ?

 

[Madrizmoreno]

you have not explained which error you get

what are MoMonth and MoYear ?

Hi, thanks for your reply. the error is in the title: #value! error. MoMonth and MoYear are the names for my cells MoMonth is the name of the month, January, February etc, and MoYear is the relevant year as in 2014.

MS help tells me the error is caused by different types of data, does that mean that I would have to write months in a number? so 1 for january instead of text? BTW this is from a MS template so I would have expected it to be correct.

Thanks.

 

[Jonmo1]

Is the value of MoMonth actually just a TEXT string of January or February etc?
Or is it an actual DATE, and the cell has been formatted to only show the month as in mmmm ??

What does this return

=ISNUMBER(MoMonth)


Also for the MoYear, is that also just a date with the cell formatted as yyyy ?

 

[Jonmo1]

It works fine for me provided:
The named ranges are not just dates formatted as mmmm or yyyy
MoMonth is a TEXT STRING of the month (January, February etc)
MoYear is a number representing a year (2014)

Excel Workbook
	A	E	F	G	H	I
1			MoMonth	MoYear		13
2			February	2014
3
4
5
6	12
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Madrizmoreno]

Strange, still doesn't work for me. Momonth is a text string and Moyear a number as you said. B2 is actually Momonth, what did you used here? A6 in my case is a blank cell and when dragging the formula the previous result (the date) is filled in instead.

 

[Madrizmoreno]

Strange, still doesn't work for me. Momonth is a text string and Moyear a number as you said. B2 is actually Momonth, what did you used here? A6 in my case is a blank cell and when dragging the formula the previous result (the date) is filled in instead.

If I go through the calculation steps it seems the #value! error start with DATEVALUE.

 

[Jonmo1]

What happened here?[
QUOTE=Jonmo1;3679694]
What does this return

=ISNUMBER(MoMonth)


Also for the MoYear, is that also just a date with the cell formatted as yyyy ?[/QUOTE]

 

[Madrizmoreno]

ISNUMBER(MOMonth) is False which is correct right as it should be TEXT. ISNUMBER(MoYear) is Number. MoYear has no formatting (general). If I change to yyyy, I can see in the formula bar that it converts 2013 into dd/mm/yy and year being 1905!.

 

[Madrizmoreno]

ISNUMBER(MOMonth) is False which is correct right as it should be TEXT. ISNUMBER(MoYear) is Number. MoYear has no formatting (general). If I change to yyyy, I can see in the formula bar that it converts 2013 into dd/mm/yy and year being 1905!.

I mean ISNUMBER(MoYear)=True