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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
you have not explained which error you get

what are MoMonth and MoYear ?
 

Madrizmoreno

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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>
 

Madrizmoreno

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

New Member
Joined
Jan 7, 2014
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

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
Top