Fixing #Value error message

[Detectiveclem]

Help please, I am using the following formula which works perfectly when someone enters their actual date of birth in cell D18, but until they do I want the text as shown below to show in cell D18, but do not want the error code shown to appear. I would prefer it remained blank or said "Auto Polulated".

I have tried playing with IFERROR but can't find the correct way to write the formula. Any help would be greatly appreciated.

=IF(D18>0,DATEDIF(D18,TODAY(),"Y"),"")

 

[fadee2]

hi detectiveclem,
The syntax of your formula appears to be correct and should not show any errors. in case if it does, then use the below:

=iferror(IF(D18>0,DATEDIF(D18,TODAY(),"Y"),""),"")

hope it helps.

P.s. if this does not perform as required, then I would suggest sharing your worksheet via xl2bb.

 

[Detectiveclem]

=iferror(IF(D18>0,DATEDIF(D18,TODAY(),"Y"),""),"")

Hi fadee2, works perfectly, resolves my issue. Thank, have a great day. Paul

 

[fadee2]

You're welcome.
glad it helped.

 

[Fluff]

Another option

=IF(ISNUMBER(D18),DATEDIF(D18,TODAY(),"Y"),"")

This way you will still get an error if somebody puts in a date in the future.

 

[Detectiveclem]

Another option

=IF(ISNUMBER(D18),DATEDIF(D18,TODAY(),"Y"),"")

This way you will still get an error if somebody puts in a date in the future.

Hi again Fluff,

The solution provided by fadee2 works. Will your solution work if someone makes a mistake and puts a date in the future?

 

[Fluff]

It will show an error if the date is in the future, thereby alerting you to the fact that there is a problem.
Which option you use is down to personal preference.

 

[Detectiveclem]

It will show an error if the date is in the future, thereby alerting you to the fact that there is a problem.
Which option you use is down to personal preference.

Thanks fluff, I would prefer an error message

 

[Fluff]

You're welcome & thanks for the feedback.