# Calculating Gestational Age

#### sejohnson

##### New Member
Hello All,

I am creating an outpatient log using Excel for a Labor & Delivery Unit. Is there a way to calculate a gestational age by entering the due date?

Ideally a staff member would enter the EDC (est. date of confinement), say 3/31/2012 in A2 and the EGA (est. gestational age) would generate in B2 as "38+3," "38 3/7" or "38 weeks 3 days".

Thanks in advance for any input!

#### Robert Mika

##### MrExcel MVP
I have taken 279 as the total day of pregnancy:
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">31-Mar</td><td style=";">38 weeks 3 days</td></tr></tbody></table>
Sheet12

#### MARK858

##### MrExcel MVP
Robert, that's much neater/better than the ugly way I did it. But seeing as I typed it then I thought I might as well post it (even though you would mad to use it over your method).

Excel Workbook
ABC
1 31/03/2012
221/03/2012
 Sheet1

#### sejohnson

##### New Member
Thanks for the replies!

I used your solution, Robert, and it works perfectly.

However, I do have one more query -

I've pasted the formula to an entire column to accommodate multiple future entries. However, the EGA column (B2) now reads 5895 weeks, 3 days in every cell (the result changes when I enter an EDC and gives the correct, updated EGA).

Is there a way to hide the result until the actual EDC is entered?

Thanks!

#### SusaninHouston

##### Active Member
The easiest way is to say (where cell A1 has your date)

If(or(A1=0,A1=""),0,Robert's formula)

That will correct for blanks and return a 0.

#### Robert Mika

##### MrExcel MVP
Robert, that's much neater/better than the ugly way I did it. But seeing as I typed it then I thought I might as well post it (even though you would mad to use it over your method).

Excel Workbook
ABC
1 31/03/2012
221/03/2012
 Sheet1
Hi Mark,
Every apprach counts.
That will give the same result as Susan suggestion:

=IF(ISBLANK(A1),"",FLOOR(279-(A1-TODAY()),7)/7&" weeks "&MOD(279-(A1-TODAY()),7)&" days")

#### CamDoll

##### New Member
Hi all!

I am also needing to calculate gestational age but in a slightly different way. I've played around with these formulas but can't seem to get it to work the way I need it.

Background info:
System: Office 2011 for Mac
I'm working on a clinical trial involving pregnant women where I have to follow them at each of their prenatal appointments. Most providers track gestation using the expected due date however, for my purposes I need to track my patients by gestational age in weeks and days format (ex: 21w3d).

Currently we are using a basic list of dates of each "projected" appointment date. Each projected date is set exactly 4 weeks from the last date based on the first date they enroll in the study. I would like to be able to add a field next to each date with the what the gestational age would be based on the expected delivery date.

Hopefully I explained this well. Any help is appreciated!

Side note: Ideally I would have the projected appointment date turn red (or gray) if it fell on a weekend. Though the gestational age is more important!

-Camille

#### barry houdini

##### MrExcel MVP
Hello Camille, welcome to MrExcel

If you have expected delivery date in B1 and an appointment date A2 then try this formula

=INT((279-\$B\$1+A2)/7)&"w"&MOD(279-\$B\$1+A2,7)&"d"

#### romdos

##### New Member
Works great! But I have another question: I'm doing a high risk list and need the formula to go down the column, i.e. "A" column has a list of patient names, the "B" column is Due Date, and the "C" column is estimated gestational age. How would I change the formula to work in every row, taking the data from the due date and calculating the gestational age in the row to the right, then going to the next row with a new patient and calculating their info? Thanks in advance!

#### shg

##### MrExcel MVP
Like this?

 A​ B​ C​ D​ 1​ Name​ Due​ GA [w.d]​ 2​ Elizabeth 07/15/2014​ 33.0​ C2 and down: =DOLLARFR((TODAY()-B2+280)/7, 7) 3​ Barb 09/26/2014​ 22.4​ 4​ Cara 11/09/2014​ 16.2​ 5​ Dana 12/11/2014​ 11.5​ 6​ Ella 10/04/2014​ 21.3​ 7​ Fran 08/27/2014​ 26.6​ 8​ Gail 07/23/2014​ 31.6​ 9​ Hana 10/15/2014​ 19.6​ 10​ Iris 12/01/2014​ 13.1​

Last edited:

1,081,729
Messages
5,360,930
Members
400,602
Latest member
newaqua

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...