Corrected Gestational Age based on GA at birth

hbcbs

New Member
Joined
Jun 10, 2015
Messages
5
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?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 10, 2015
Messages
5
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
You want the corrected age in weeks.days, or decimal months, or something else?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,838
Messages
5,598,383
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top