Calculating Weeks/Days from date in past to today
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!
Best Answer

#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
Answers

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.

#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

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"

That looks perfect to me, well done! 😀
Help Article Resources
Categories
Check out the Formula Handbook template!