Nesting DATEDIF and IF Statements in Excel

user_18271545 0
Viewed 36

I am trying to create an Excel formula that will display someone's age in units of days, weeks, months, or years, depending on how old they were at the time of a test. To clarify...

  • If younger than 7 days, report the age in days
  • If older than 7 days but younger than 8 weeks, report the age in weeks
  • If older than 8 weeks but younger than 24 months, report the age in months
  • If older than 24 months, report the age in years

I have tried several functions for this, all using the DATEDIF function. For each, I have returned the #NAME? error. Please see below

KEY:

  • E600 = Date of Test
  • F600 = Date of Birth
  • I am using 7 days = 1 week, 60 days = 2 months, 730 days = 24 months = 2 years, etc.
  • Note: I also want to display "d/o" (days old), "w/o" (weeks old), "m/o" (months old), or "y/o" (years old) after each value, depending on the units that age is being reported in.

\\\\\\\\\\\\

=IFS((E600-F600)<7,DATEDIF(F600,E600,”d”)&” d/o”,AND((E600-F600)>7,(E600-F600)<60),(DATEDIF(F600,E600,”d”)/7)&” w/o”,AND((E600-F600)>60,(E600-F600)<730),DATEDIF(F600,E600,”m”)&” m/o”,(E600-F600)>730,DATEDIF(F600,E600,”y”)&” y/o”)

\\\\\\\\\\\\\

=IFS(DATEDIF(F600,E600,”d”)<=6,DATEDIF(F600,E600,”d”)&” d/o”,AND(DATEDIF(F600,E600,”d”)<=59,DATEDIF(F600,E600,”d”)>=7),DATEDIF(F600,E600,”d”)/7&” w/o”,AND(DATEDIF(F600,E600,”d”)>=60,DATEDIF(F600,E600,”m”)<=24),DATEDIF(F600,E600,”m”)&” m/o”,DATEDIF(F600,E600,”m”)>25,DATEDIF(F600,E600,”y”)&” y/o”)

\\\\\\\\\\\\\

=DATEDIF(F600,E600,IFS((E600-F600)<7,”d”&” d/o”,AND((E600-F600)>7,(E600-F600)<60),”d”/7&” w/o”,AND((E600-F600)>60,(E600-F600)<730),”m”&” m/o”,(E600-F600)>730,”y”&” y/o”))

\\\\\\\\\\\\\\

Any help on this would be greatly appreciated!

datediff if-statement excel-formula excel
2 Answers

=LOOKUP(E600-F600,{0,8,56,730},INT(DATEDIF(F600,E600,{"d","d","m","y"})/{1,7,1,1})&" "&{"d","w","m","y"}&"/o")

though your listed criteria state only "if younger than", "if older than", so it's not clear in which category someone who, for example, is precisely 7 days old should lie.

user_170077040

@Jos Woolley's example worked great. Another solution is posted below if anyone is troubleshooting their own formula in the future.

=IF(E600 < F600 + 7, DATEDIF(F600, E600, "d") & " d/o", IF(AND(E600 >= F600 + 7, E600 <= EDATE(F600, 2)), ROUNDDOWN(DATEDIF(F600, E600, "d") / 7, 0) & " w/o", IF(AND(E600 >= EDATE(F600, 2), E600 <= EDATE(F600, 24)), DATEDIF(F600, E600, "m") & " m/o", DATEDIF(F600, E600, "y") & " y/o")))
user_182715450
Related