Calculating Gestational Age

[sejohnson]

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]

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​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">B1</th><td style="text-align:left">=FLOOR(279-(A1-TODAY()),7)/7&" weeks "&MOD(279-(A1-TODAY()),7)&" days"</td></tr></tbody></table></td></tr></tbody></table>

 

[MARK858]

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
	A	B	C
1	26/06/2011		31/03/2012
2	21/03/2012	38 week(s) and 3 day(s)
Sheet1

 

[sejohnson]

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]

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]

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
	A	B	C
1	26/06/2011		31/03/2012
2	21/03/2012	38 week(s) and 3 day(s)
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]

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]

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]

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]

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​