On Tue, 26 Aug 2014 21:07:51 +0100, zomccoy wrote:


Hi,
I work in a hospital providing support for families of premature
infants. I use an excel spreadsheet to track the patients that I work
with. I'm hoping someone can assist me in creating a formula for my
spreadsheet to display an updated baby's age based on today's date.
For instance, I would enter a baby's birthday in "a1", the baby's
gestational age at birth in "a2" (ex: 29 weeks 2 days or 29 2/7), and
then have a formula in "a3" that tells me how old they are today in
weeks.
Is this possible?
Example: A baby is born on 8/20/2014 at 30 weeks 2 days. I need a
formula to show me that on today's date (8/26/2014) the baby is now 31
weeks 1 day old. I'd like it to display as 31 weeks 1 day or 31 1/7.

Thanks so much!


For 31 1/7 output:

=TEXT((TODAY()-A1+LEFT(A2,FIND(" ",A2)-1)*7+TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*2,99)))/7,"0 ?/7")

The weeks days format is more complex, and I also adjust for the fact that, other than 1, day would be plural.

=TEXT(INT((TODAY()-A1+LEFT(A2,FIND(" ",A2)-1)*7+TRIM(
MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*2,99)))/7),"0 ""weeks """) &
TEXT( MOD((TODAY()-A1+LEFT(A2,FIND(" ",A2)-1)*7+TRIM(
MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*2,99))),7)," 0 ""day""") &
IF(MOD(C2,7)<1,"s","")