Forum Discussion

Copper Contributor

Sep 23, 2021

Solved

calculating dob for daycare list when child isn't born yet

I have a spreadsheet that calculates the DOB of a child based on todays date and DOB - what do I do if it's also a waiting list because the child isn't born yet? I get a #NUM! error. I'm thinking it ...

Formulas and Functions

JSHEP73
Sep 24, 2021
BBacon621 , just wrap your existing formula with the IFERROR formula,

=IFERROR(DATEDIF(B2,TODAY(),"y")&" year(s), "&MOD(DATEDIF(B2,TODAY(),"m"),12)&" month(s)",0)

HansVogelaar

MVP

Sep 23, 2021

BBacon621

For example:

=IF(B2="","Not born yet",DATEDIF(B2,TODAY(),"y")&" year(s), "&MOD(DATEDIF(B2,TODAY(),"m"),12)&" month(s), "&TODAY()-EDATE(B2,DATEDIF(B2,TODAY(),"m"))&" day(s)")

=IF(B2="","0 years, 0 months, 0 days",DATEDIF(B2,TODAY(),"y")&" year(s), "&MOD(DATEDIF(B2,TODAY(),"m"),12)&" month(s), "&TODAY()-EDATE(B2,DATEDIF(B2,TODAY(),"m"))&" day(s)")

BBacon621

Copper Contributor

Sep 23, 2021

Both work to give me the year, month, days BUT neither work if the child isn't born yet. If the child isn't born till lets say October 13, 2022 then I'm still getting the #NUM! error 😞

HansVogelaar
MVP
Sep 23, 2021
BBacon621

How do you know the DOB if the child isn't born yet? Anyway, try

=IF(B2>TODAY(),"0 years, 0 months, 0 days",DATEDIF(B2,TODAY(),"y")&" year(s), "&MOD(DATEDIF(B2,TODAY(),"m"),12)&" month(s), "&TODAY()-EDATE(B2,DATEDIF(B2,TODAY(),"m"))&" day(s)")