Correcting #value! Problem

howard

Well-known Member
I have the following formula in cell J1

=IF(DATE("20"&MID(F3,4,2),MID(F3,7,2),28)>DATE("20"&MID(I3,4,2),MID(I3,7,2),28),"Y",IF(DATE("20"&MID(F3,4,2),MID(F3,7,2),28)=DATE("20"&MID(I3,4,2),MID(I3,7,2),28),"CURR","N/A"))

It gives the result #value!. I think that I need to include a text function within the above formula, but I not sure how to incorporate this within another formula in order for the error to be rectified

I have also attached some text data referring to the respective cell numbers

Your assistance will be most appreciated

Management Accounts.xls
FGHIJ
115/03/2011*CHKCLUMN*24/02/2011#VALUE!
2
TB CONSOLIDATION

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

matthewjapp

Board Regular
Use

=if(iserror(.......,"Whatever",IF(DATE("20"&MID(F3,4,2),MID(F3,7,2),28)>DATE("20"&MID(I3,4,2),MID(I3,7,2),28),"Y",IF(DATE("20"&MID(F3,4,2),MID(F3,7,2),28)=DATE("20"&MID(I3,4,2),MID(I3,7,2),28),"CURR","N/A"))

UniMord

Active Member
Use Evaluate Formula to see where the error is. It appears your problem is in the values entered in the Date function.

Last edited:

howard

Well-known Member
Hi Guys

Thanks for the help

Replies
2
Views
99
Replies
5
Views
537
Replies
3
Views
161
Replies
1
Views
207
Replies
2
Views
245

1,195,849
Messages
6,011,957
Members
441,657
Latest member
Diupsy

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.

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

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