SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2779253%22%20slang%3D%22en-US%22%3Ecalculating%20dob%20for%20daycare%20list%20when%20child%20isn't%20born%20yet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2779253%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20calculates%20the%20DOB%20of%20a%20child%20based%20on%20todays%20date%20and%20DOB%20-%20what%20do%20I%20do%20if%20it's%20also%20a%20waiting%20list%20because%20the%20child%20isn't%20born%20yet%3F%20I%20get%20a%20%23NUM!%20error.%20I'm%20thinking%20it%20would%20be%20an%20IF%20statement%20but%20not%20sure%20how%20to%20get%20it%20to%20say%20%220%22%20in%20the%20DOB%20field%20and%20change%20to%20the%20right%20age%20once%20the%20child%20is%20born%3F%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20my%20formula%20so%20far%3A%3C%2FP%3E%3CP%3E%3DDATEDIF(B2%2CTODAY()%2C%22y%22)%26amp%3B%22%20year(s)%2C%20%22%26amp%3BMOD(DATEDIF(B2%2CTODAY()%2C%22m%22)%2C12)%26amp%3B%22%20month(s)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Egives%20me%20the%20correct%20age%20in%20years%20%26amp%3B%20months%20-%20if%20the%20child%20isn't%20born%20yet%20I'd%20like%20it%20to%20say%20%220%20years%2C%200%20months%22%20and%20then%20change%20once%20the%20child%20is%20born%20to%20say%20%220%20years%2C%200%20months%2C%201%20day%22%20etc.%20Is%20that%20even%20possible%3F%20It's%20okay%20if%20it%20uses%20years%2C%20months%2C%20days%20for%20all%20the%20children%20if%20it%20has%20to%20be%20all%20or%20nothing...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20help%20-%20I%20love%20excel%20but%20don't%20understand%20a%20lot%20about%20manipulating%20complex%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBeth%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2779253%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2779452%22%20slang%3D%22en-US%22%3ERe%3A%20calculating%20dob%20for%20daycare%20list%20when%20child%20isn't%20born%20yet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2779452%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1155863%22%20target%3D%22_blank%22%3E%40BBacon621%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(B2%3D%22%22%2C%22Not%20born%20yet%22%2CDATEDIF(B2%2CTODAY()%2C%22y%22)%26amp%3B%22%20year(s)%2C%20%22%26amp%3BMOD(DATEDIF(B2%2CTODAY()%2C%22m%22)%2C12)%26amp%3B%22%20month(s)%2C%20%22%26amp%3BTODAY()-EDATE(B2%2CDATEDIF(B2%2CTODAY()%2C%22m%22))%26amp%3B%22%20day(s)%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(B2%3D%22%22%2C%220%20years%2C%200%20months%2C%200%20days%22%2CDATEDIF(B2%2CTODAY()%2C%22y%22)%26amp%3B%22%20year(s)%2C%20%22%26amp%3BMOD(DATEDIF(B2%2CTODAY()%2C%22m%22)%2C12)%26amp%3B%22%20month(s)%2C%20%22%26amp%3BTODAY()-EDATE(B2%2CDATEDIF(B2%2CTODAY()%2C%22m%22))%26amp%3B%22%20day(s)%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2779573%22%20slang%3D%22en-US%22%3ERe%3A%20calculating%20dob%20for%20daycare%20list%20when%20child%20isn't%20born%20yet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2779573%22%20slang%3D%22en-US%22%3EBoth%20work%20to%20give%20me%20the%20year%2C%20month%2C%20days%20BUT%20neither%20work%20if%20the%20child%20isn't%20born%20yet.%20If%20the%20child%20isn't%20born%20till%20lets%20say%20October%2013%2C%202022%20then%20I'm%20still%20getting%20the%20%23NUM!%20error%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 would be an IF statement but not sure how to get it to say "0" in the DOB field and change to the right age once the child is born????

 

this is my formula so far:

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

 

gives me the correct age in years & months - if the child isn't born yet I'd like it to say "0 years, 0 months" and then change once the child is born to say "0 years, 0 months, 1 day" etc. Is that even possible? It's okay if it uses years, months, days for all the children if it has to be all or nothing...

 

please help - I love excel but don't understand a lot about manipulating complex formulas

 

Beth

5 Replies

@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)")

 

or

 

=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)")

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

@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)")

best response confirmed by BBacon621 (Occasional Contributor)
Solution

@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)

thank you that worked!