Calculating gestational age at time of pregnancy test from date of birth and gestational age at time of birth

[KristyK]

Hi All,

I am hoping someone can help me design a formula for my excel project. THANKS in advance!

I am required to calculate the gestational age of a baby (in weeks and days) at a particular point in the pregnancy and also after birth. I have the date of birth of the baby and the gestational age on the day of birth.

Example -

• Date of birth - 20/03/2015
• Gestational age at time of birth - 37+6 weeks
• Date of maternal test during pregnancy - 13/03/2015
• Gestational age at time of maternal test during pregnancy - ??x (answer would be 36+6 weeks)
• Date of baby's test after birth - 30/04/2015
• Gestational age at time of baby's test after birth - ??y (answer would be 43+5 weeks)

Please note that the gestational age needs to be in weeks and days out of 7 (ie 36+7 actually equals 37+0 weeks)

I really appreciate any help.
Thanks again,
Kristy.

 

[etaf]

i have used a few helper columns
to calculate some key days
[TABLE="width: 636"]
<colgroup><col span="4"><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 6"]Helper[/TD]
[/TR]
[TR]
[TD]Mert G Age[/TD]
[TD]Birth G Age[/TD]
[TD] [/TD]
[TD]G birth days[/TD]
[TD]mat. test days[/TD]
[TD]Birth test Days[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]41[/TD]
[TD][/TD]
[TD]265[/TD]
[TD]258[/TD]
[TD]306[/TD]
[/TR]
</tbody>[/TABLE]

then used
=INT((M3/7))&"+"&(MOD(M3,7))

shame i cannot use any tools that work on my Mac ( i have posted about this in past)
so here is a link to a dropbox file
https://www.dropbox.com/s/ps1xtn4y50hvqeg/Gstational_etaf.xlsx?dl=0

 

[AhoyNC]

Maybe this link.
https://www.mrexcel.com/forum/excel-questions/623070-calculating-gestational-age.html

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Event​

[/td][td="bgcolor:#F3F3F3"]

Date​

[/td][td="bgcolor:#F3F3F3"]

GA​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Birth[/td][td]

20 Mar 2015​

[/td][td]

37.6​

[/td][td]C2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Test 1[/td][td]

13 Mar 2015​

[/td][td="bgcolor:#CCFFCC"]

36.6​

[/td][td="bgcolor:#CCFFCC"]C3: =DOLLARFR((DOLLARDE(C$2, 7) * 7 - B$2 + B3) / 7, 7)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Test 2[/td][td]

30 Apr 2015​

[/td][td="bgcolor:#CCFFCC"]

43.5​

[/td][td][/td][/tr]
[/table]

 

[KristyK]

Thanks shg,

Formula works well but do you know how to copy the formula down the column to automatically change to correspond the number after the "$" row you are working on? It changes the number in B3 automatically but not the ones after the $ symbols. Sorry I'm not very proficient with the workings of excel!

PS. I have everything on the same row.

Thanks in advance!

 

[KristyK]

Oh don't worry I just figured it out - remove the $!