Help on adding an IFERROR/IF function to an existing formula.

[Albert Dirk]

Help on adding an IFERROR/IF function to an existing formula.

Okay I have attached my document if you open it you will see I have formulas already in place (the age in words column does not work) that works from the ID Number column.

What I want Excel to do is the cells that are red to leave the cells open and not give me an error or wrong value.

The reason why the line for MAX is giving me errors and wrong values, are because he has a passport number and not an ID number.

Can you also help me with the “Age in Words” column please if it is possible with an IF Function?

Thanks in advance for your help…

[Richard Buttrey]

Hi,

For the #VALUE results wrao the formula in an IFERROR.
e.g.

Please Login or Register  to view this content.

For F4:

Please Login or Register  to view this content.

I've added Microsoft's User Defined Function 'SpellNumber' hence your workbook is now a .xlsm file.

[JohnTopley]

The "Age in Words" uses a VBA macro "SpellNumber" which is not present in the sheet.

in E3

=IFERROR(DATE(LEFT(D3,2),MID(D3,3,2),MID(D3,5,2)),"")

in G3

=IFERROR(INT((TODAY()-DATE(LEFT(D3,2)+1900,MID(D3,3,2)+0,MID(D3,5,2)+0))/365.25),"")

in J3

=IFERROR(DATE(YEAR(E3)+DATEDIF(E3,I3,"y")+1,MONTH(E3),DAY(E3)),"")

in K3

=IFERROR(DATEDIF(I3,J3,"d"),"")

[BadlySpelledBuoy]

IFERROR added to your formulas in the attached.

As for spelling out numbers as words, I'm not even sure why you would want to do that considering the numbers are right there and far easier to read than long winded word versions.
Having said that it is possible, although not straightforward. Perhaps the link below will help...
https://support.microsoft.com/en-us/kb/213360

BSB

[José Augusto]

Hi
To get a spell number see this link https://support.microsoft.com/en-us/kb/213360
(How to convert a numeric value into English words in Excel)

[jason.b75]

In E3, and copied down.

=IFERROR(DATE(LEFT(D3,2),MID(D3,3,2),MID(D3,5,2)),"")

For the other columns, add an extra IF to each formula in row 3, then copy them down.

=IF(E3="","",existing formula)

This will blank the rest of the row when column E is blank as the result of an error.

[Albert Dirk]

Thanks allot everyone I appreciate the help.

The reason why the age number and words must be there is unknown to me, the company I work for has given the instruction for it to be like that and it is not my personal preference in this matter.

[Richard Buttrey]

Glad to have helped and thanks for the rep.