Calculating Weeks/Days from date in past to today

arw88

Hello, another question!

I am trying to come up with a formula to count gestational age in weeks and days. So far I have figured that "=NETDAYS([LMP Calc]@row, TODAY()) / 7" will give me a number (19.42857). Is there a way to express that as 19 weeks and X amount of days? I have tried several methods on the forums and also tried the AI formula generator with no luck on this one. Thanks in advance!

KPH

#1. The INT here does nothing as today minus a date will always be a whole number so dividing the whole number by 7 is the same as dividing the integer by 7

=INT(TODAY() - [LMP Actual]@row) / 7 returns 19.42857

#2 and #3 Are good

In #4 you want to multiply the 0.42857 by 7 to convert weeks to days.

Try this:

=(((TODAY() - [LMP Actual]@row) / 7) - INT((TODAY() - [LMP Actual]@row) / 7)) * 7

KPH

Hi @arw88

The INT function will do what you need.

=INT(your formula) will return the weeks, ie 19

=(your formula) - INT(your formula) will give you parts of weeks, ie 0.42857

Multiply that by 7 to get days

=((your formula) - INT(your formula) ) x 7

arw88

Thank you! That makes sense, but not sure why I am still having trouble.

#1. =INT(TODAY() - [LMP Actual]@row) / 7 returns 19.42857

#2. Placing the entire equation in parenthesis =INT((TODAY() - [LMP Actual]@row) / 7) returns 19

#3. =((TODAY() - [LMP Actual]@row) / 7) - INT((TODAY() - [LMP Actual]@row) / 7) returns 0.42857

So far so good on getting the full number, and the two numbers (weeks/days) separated.

When I do

#4. =((TODAY() - [LMP Actual]@row) / 7) - INT((TODAY() - [LMP Actual]@row) / 7) x 7, I get #UNPARSEABLE

I've tried a few different versions to see if I am messing up /7 or x7 placement, but still getting the same error code. I'm sure I am just missing something logical somewhere.

KPH

#1. The INT here does nothing as today minus a date will always be a whole number so dividing the whole number by 7 is the same as dividing the integer by 7

=INT(TODAY() - [LMP Actual]@row) / 7 returns 19.42857

#2 and #3 Are good

In #4 you want to multiply the 0.42857 by 7 to convert weeks to days.

Try this:

=(((TODAY() - [LMP Actual]@row) / 7) - INT((TODAY() - [LMP Actual]@row) / 7)) * 7

arw88

Thank you, so much!!

I used

=INT((TODAY() - [LMP Actual]@row) / 7) for column Weeks Gestation

=(((TODAY() - [LMP Actual]@row) / 7) - INT((TODAY() - [LMP Actual]@row) / 7)) * 7 for column Days Gestation

and

=INT((TODAY() - [LMP Actual]@row) / 7) + " weeks, " + (((TODAY() - [LMP Actual]@row) / 7) - INT((TODAY() - [LMP Actual]@row) / 7)) * 7 + " days" to return Gestation Wks & Days in the format of "19 weeks, 3 days"

KPH

That looks perfect to me, well done! 😀