Dates in Weeks and Days

[hbcbs]

Hello I am stuck. I am trying to make a formula to calculate a premature baby's corrected gestational age using todays date (L1), Baby's Birthdate (D27) and Gestational age at birth (F27).
Baby's gestational age is in the WW.D format. I THINK I am placing F27 in the wrong place because some of my results are resulting in 35.9 which cannot be because there aren't 9 days in 1 week. Can anyone help, please?

Here is what I have:



=IF(ISBLANK(F27),"",(+INT(DATEDIF(D27,$L$1,"D")/7)&"."&MOD(DATEDIF(D27,$L$1,"D"),7))+F27)

 

[Dave Patton]

The result of 35.9 could be correct; review the sub parts of your formula.
Consider
=IF(ISBLANK(F27),"",(INT(($L$1-D27)/7)&"."&MOD($L$1-D27,7))+F27)

If you can secure the number of days that yield the result in F27, you could modify the formula.
I put the number of days in G27

=IF(ISBLANK(F27),"",(INT(($L$1-D27+G27)/7)&"."&MOD($L$1-D27+G27,7)))

This would yield 36.2

I calculated G27 with =INT(F27)*7+(F27-INT(F27))*10 but you probably have the number of days
or you could calculate the number of days from the dates on your spreadsheet.

 

[hbcbs]

Thank you Dave. I made a new field G27 to count the number of days and applied your formula. SPOT ON. THANK YOU THANK YOU! You just helped a bunch of nurses save a bunch of time!

 

[Dave Patton]

N.B. I do not know your data layout or actual requirements.

A simpler solution may be =INT(($L$1-C27)/7)&"."&MOD($L$1-C27,7) or
=INT((dToday-dBeg)/7)&"."&MOD(dToday-dBeg,7) or
=DOLLARFR((dToday-dBeg)/ 7, 7)

- providing answer in www.dd format
- using day beginning and day today.

 

[mihai6900]

Hi,

I have something similar that I need help with.

I have a Date of Birth (A), the Gestational Age at birth (B) in weeks.days and I would like to calculate the gestational age in weeks.days at a later date (C).

I used C=B + DOLLARFR((C-A)/7,7) but because of the format of B I get numbers like 32.8 (only 7 days in the week). Is there a way to correct for this?
Thanks

 

[Dave Patton]

I would work with days not week.days.

You could try

=DOLLARFR((Gestation_Age_Days+Later_Days)/ 7, 7)

A concise example with your expected results would help.

 

[mihai6900]

Thanks Dave,

Right now I have gestational age at birth in weeks.days format (ie. 36.4). How would I convert that age into days?

 

[Dave Patton]

The answer is earlier in this thread.

Did you try any of the examples or read Excel's help for the functions?

Try something like

=INT(D5)*7+(D5-INT(D5))*10

 

[mihai6900]

Much appreciated. After posting my question I was able to figure it out using the Excel help.
Happy Holidays!