Forum Discussion
#NUM! error - how do I make it Zero?
I have a document where I need to work out the difference between two dates. this will be duplicated up to 1000 times, and needs to be dynamic for adding different dates in. but I am getting a NUM value due to the example below because the start date is after the 22 years of age cell. I need it to say Zero in this instance, as further in the spreadsheet I need to add multiple numbers up. Cell Y6 being one of them.
CELL KEY
DOB = V6
Start date = W6
22 Yoa - X6
Years to pay = Y6 #NUM! value one
The formulas I have used are as follows:
22 yoa =(DATE(YEAR(V6)+22,month(V6),DAY(V6))
Years to pay =DATEDIF(W6,X6,"y")
| DOB | Start Date | 22 yoa | years to pay |
| 14/12/1972 | 27/03/1995 | 14/12/1994 | #NUM! |
Any help much appreciated, Im scratching my head, and really dont want to have to work all these numbers out by hand.
Thanks in advance
3 Replies
- JKPieterseSilver ContributorYou could use:
=IFERROR(DATEDIF(W6,X6,"y"),0)- Debby BeattieCopper Contributormany thanks for your help, much appreciated
- JKPieterseSilver ContributorYou're welcome!