Calculate estimated delivery date by gestational age at date of delivery

[maggieph]

I have a spreadsheet with date of delivery and gestational age at delivery and I want to calculate the estimated delivery date based on those two inputs. e.g. date of delivery 23/06/2019, gestational age at delivery 36W 6D, ?? estimated delivery date

 

[6StringJazzer]

I am confused. If you have the date of delivery as an input, how are you determining estimated delivery date? Aren't they the same thing?

Please show a couple of examples.

 

[maggieph]

So the date of delivery is the date when delivery actually happened and this might occur at any point in the pregnancy. The estimated delivery date is calculated at the beginning of the pregnancy. It is a future date when there are 40 completed weeks of pregnancy.

In my data sheet I have
The delivery date 23/06/2019
Gestational age at delivery 36W 6D
I want to calculate the estimated delivery date when the woman would be 40 weeks 23/06/2019 plus 22 days in this case

 

[6StringJazzer]

Error, will repost in a minute

 

[6StringJazzer]

So you want to take the actual delivery date, back it up according to gestational age, then go forward again by 40 weeks to get after-the-fact estimated delivery date?

(Dates in U.S. format)

$scratch.xlsm
	A	B	C
1	Delivery Date	Gestational Age	Estimated Delivery Date
2	6/23/2019	36W 6D	7/15/2019
delivery

Cell Formulas
Range	Formula
C2	C2	=A2+7*40-(7*LEFT(B2,FIND("W",B2)-1)+SUBSTITUTE(SUBSTITUTE(B2,LEFT(B2,FIND("W",B2)-1)&"W ",""),"D",""))

 

[6StringJazzer]

Slightly shorter version taking advantage of LET function, which I believe is supported in 2021.

=LET(Weeks,LEFT(B2,FIND("W",B2)-1),A2+7*40-(7*Weeks+SUBSTITUTE(SUBSTITUTE(B2,Weeks&"W ",""),"D","")))