Help with #value! error

Madrizmoreno

New Member
Joined
Jan 7, 2014
Messages
6
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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 ?
 
Upvote 0
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
AEFGHI
1MoMonthMoYear13
2February2014
3
4
5
612
Sheet1
#VALUE!
</td></tr></table></td></tr></table>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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!.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top