Corrected Gestational Age based on GA at birth

[hbcbs]

HELP!! (I'm trying to replicate a formula on a locked worksheet)
I need to calculate Corrected Gestational Age for a premature baby based on Date of Birth and gestational age at birth.

B1 is Birthdate
C2 is Gestational Weeks at Birth
D2 is Gestational Days at Birth

for current gestational weeks I have: =INT(MOD((TODAY()-D4+F4,7))
and I cant seem to figure out current gestational days.

am I even going about this in the simplest way?

 

[shg]

Maybe ...

Row\Col	A​	B​	C​
1​		As Of​
2​		06/10/2015​
3​	DoB​	w.d​	​
4​	15 Aug 2014​	42.5​	B4: =DOLLARFR((B$2-A4)/ 7, 7)
5​	26 Aug 2014​	41.1​
6​	08 Sep 2014​	39.2​
7​	17 Sep 2014​	38.0​
8​	25 Sep 2014​	36.6​

... where 42.5 means 42 weeks 5 days.

 

[hbcbs]

I love that simpler approach. But lets say a baby was born on 6/1/2015 at 23 wks and 5 day and I want to know what baby's corrected gestational age is today would this formula still work?

 

[shg]

You want the corrected age in weeks.days, or decimal months, or something else?

 

[hbcbs]

it was perfect in the weeks.days format. thank you so much!

 

[shg]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​				As Of​
2​				06/10/2015​
3​	Kid #​	DoB​	Gest Age @ Birth​	Raw Age​	Corrected​	​	​
4​	1​	17 Dec 2014​	24.0​	25.0​	12.0​		C4: =DOLLARFR((C$2-A4)/ 7, 7)
5​	2​	17 Dec 2014​	24.6​	25.0​	12.6​		D4: =DOLLARFR(C4 - (B4 < 37) * (37 - DOLLARDE(B4, 7)), 7)
6​	3​	17 Dec 2014​	25.5​	25.0​	13.5​
7​	4​	17 Dec 2014​	26.4​	25.0​	14.4​
8​	5​	17 Dec 2014​	27.3​	25.0​	15.3​
9​	6​	17 Dec 2014​	28.2​	25.0​	16.2​
10​	7​	17 Dec 2014​	29.1​	25.0​	17.1​
11​	8​	17 Dec 2014​	29.7​	25.0​	17.7​
12​	9​	17 Dec 2014​	30.6​	25.0​	18.6​
13​	10​	17 Dec 2014​	31.5​	25.0​	19.5​
14​	11​	17 Dec 2014​	32.4​	25.0​	20.4​
15​	12​	17 Dec 2014​	33.3​	25.0​	21.3​
16​	13​	17 Dec 2014​	34.2​	25.0​	22.2​
17​	14​	17 Dec 2014​	35.1​	25.0​	23.1​
18​	15​	17 Dec 2014​	36.0​	25.0​	24.0​
19​	16​	17 Dec 2014​	36.6​	25.0​	24.6​
20​	17​	17 Dec 2014​	37.5​	25.0​	25.0​

That says if a kid is born prior to 37 weeks, the difference get subtracted from the age.