Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Updating gestational age based on today's date
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating gestational age based on today's date
Hi,
Am Tue, 26 Aug 2014 21:07:51 +0100 schrieb zomccoy: 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. in A1: 8/20/2012 in A2: 30 weeks 2 days Then try: =INT((TODAY()-A1+LEFT(A2,FIND(" ",A2)-1)*7+MID(A2,FIND("weeks",A2)+6,1))/7)&" weeks "&MOD(TODAY()-A1+LEFT(A2,FIND(" ",A2)-1)*7+MID(A2,FIND("weeks",A2)+6,1),7)&" days" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating gestational age based on today's date
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","") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Color of Cell based on Date and today's date. | Excel Worksheet Functions | |||
multiple If's based on today's date | Excel Worksheet Functions | |||
SUM based on Date < TODAY() | Excel Discussion (Misc queries) | |||
How do I stop today() from updating date on saved spreadsheets? | Excel Discussion (Misc queries) | |||
Show a date based on today | Excel Worksheet Functions |