Calculating Weeks/Days from date in past to today

Options

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

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options


    #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

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    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
    Options

    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
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options


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

    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
    KPH ✭✭✭✭✭✭
    edited 04/10/24
    Options

    That looks perfect to me, well done! 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!