Formula for calculating corrected gestational age

[pr1ncess426]

Gestational ages are spoken of in terms of weeks and days - i.e. a baby that is 35 weeks and 4 days old would be called "35 and 4" and written as “35.4."

I am trying to create an excel formula that can populate changes in gestational age from the number of days that have passed since a first gestational age was recorded. For example:

	A	B	C	D	E
1	Date of birth	Gestational age	Today's date	Day of life	Corrected gestational age
2	6/15/17	35.4	6/20/17	5	36.2

<tbody>
</tbody>

In this example you can see that 5 days have passed since the baby was born so his corrected gestational age is now "36.2."


The number of days after the decimal point can never be more than "6" because once 7 days have passed then the gestational week changes. I am having a hard time coming up with an excel formula to calculate the new gestational ages since I can't just add the number of days to the old gestational age. Excel treats the number after the decimal point as a true number, but I need it to know that once the number after the decimal point gets above 6 then it must add one to the gestational week and the decimal point starts back at 0.


Anyone know how to handle this? Thanks!

 

[Gerald Higgins]

To add two such values, assuming one is in cell A1 and the other in A2, perhaps something like this:

=ROUNDDOWN(A1,0)+ROUNDDOWN(A2,0)+ROUNDDOWN((MOD(A1,1)+MOD(A2,1))/0.7,0)+MOD((MOD(A1,1)+MOD(A2,1))/0.7,1)*0.7

There may well be better ways of doing this.

 

[pr1ncess426]

Hi Gerald,

Thanks for your reply! Do you mean put gestational age (35.4) in A1 and day of life (5) in A2? When I do that and use your formula to solve for corrected gestational age I'm getting "40.4" - excel has simply added 5 to the original gestational age. A gestational age of "40.4" would be saying this baby was now "40 weeks and 4 days old." I need it to add 5 days - meaning it should say "36.2" or "36 weeks and 2 days."

Am I doing something wrong here?

Thanks again!

 

[xenou]

I came up with:
Int(X/7) -> the weeks
Mod(X,7) -> the days

just extend as needed. Use the Text() function to get a text result. Basically we are trying to work in sevens, since the basic problem is that the 0.4 here is not 4/10 but the remainder of a division by 7.

e.g.:
=TEXT(INT((B5+D5)/7),"#")&"."&TEXT(MOD((B5+D5),7),"#")

where B5+D5 adds the 5 days.

(it would be easier if weeks and days were in separate cells/separate columns - you could still concatenate them together for a displayed result in a third column, if that were possible).

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	1	Date of birth	Gestational age	Today's date	Day of life	Days	Corrected gestational age
2	2	15-Jun-17	35.4	20-Jun-17	5	254	36-2
3		15-Jun-17	35.4	20-Jun-17	5		36-2
4
8a

Cell Formulas
Range	Formula
E3	=D3-B3
E2	=D2-B2
F2	=INT(C2)*7+MOD(C2,1)*10+E2
G2	=INT(F2/7)&"-"&MOD(F2,7)
G3	=INT((INT(C2)*7+MOD(C2,1)*10+E2)/7)&"-"&MOD((INT(C2)*7+MOD(C2,1)*10+E2),7)

G2 uses the helper cell F2 that calculates the number of days
G3 does not use a helper cell

 

[Gerald Higgins]

In response to post #3, I was assuming a value of 5 would be input as 0.5, but maybe that was a mistake on my part.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1		Date of birth	Gestational age	Today's date	Day of life	Days	Corrected gestational age
2		15-Jun-17	35.4	20-Jun-17	5	254	36-2
3		15-Jun-17	35.4	20-Jun-17	5		36-2
4	9-Oct-16	15-Jun-17	35.4	20-Jun-17			36-2
5
8a

Cell Formulas
Range	Formula
E3	=D3-B3
E2	=D2-B2
F2	=INT(C2)*7+MOD(C2,1)*10+E2
G2	=INT(F2/7)&"-"&MOD(F2,7)
G3	=INT((INT(C2)*7+MOD(C2,1)*10+E2)/7)&"-"&MOD((INT(C2)*7+MOD(C2,1)*10+E2),7)
G4	=INT((D4-A4)/7)&"-"&MOD((D4-A4),7)

 

[shg]

	A​	B​	C​	D​	E​	F​
1​	DoB​	Gestational age​	Today​	Day of life​	CGA​	​
2​	6/15/2017​	35.4​	6/20/2017​	5​	36.2​	E2: =DOLLARFR(DOLLARDE(B2, 7) + D2/7, 7)

 

[Dave Patton]

I put shg's suggestion at the top with an example including the start date in A2.

Excel 2010
	A	B	C	D	E	F	G	H
1		Date of birth	Gestational age	Today's date	Day of life	Days	Corrected Gestational age
2	9-Oct-16	15-Jun-17	35.4	20-Jun-17	5		36.2
3	9-Oct-16	15-Jun-17	35-4	20-Jun-17		254	36-2	36-2
4	9-Oct-16	15-Jun-17	35.4	20-Jun-17	5		36.2
8aa

Cell Formulas
Range	Formula
G2	=DOLLARFR((D2-A2)/7, 7)
G4	=DOLLARFR(DOLLARDE(C4, 7)+E4/7, 7)
C2	=DOLLARFR((B2-A2)/7, 7)
C3	=INT((B3-A3)/7)&"-"&MOD((B3-A3),7)
F3	=D3-A3
H3	=INT(F3/7)&"-"&MOD(F3,7)