T-minus dates when going over to next year

Hi. We utilize a t-minus template and it works great until I move where the launch date is in next year and today's date is in this year.

=WEEKNUMBER([T-Minus Date]2) - WEEKNUMBER([T-Minus Date]3) + ""

I've tried manipulating the basic formula but to no avail. Probably a rather simple change, but I'm stumped.

A manual calc of weeks is 30 from the picture attached. But the formula comes back with 23.


Help a guy out?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/16/22

    Hi @darrell.stevens

    This is because WeekNumber is only looking at the one thing: the week in any year. I would suggest simply subtracting one date from another to get the number of days. Then you can divide this by 7 to get the estimated number of weeks:

    =ROUND(([T-Minus Date]$2 - [T-Minus Date]$3) / 7, 2) + ""

    See: Use Formulas to Perform Calculations With Dates

    I added ROUND to bring it to 2 decimal places, but you could round it up even more if you'd like:

    =ROUND(([T-Minus Date]$2 - [T-Minus Date]$3) / 7) + ""

    Cheers,

    Genevieve

  • Hey Genevieve,

    I went back and simplified the data in general. Here's my scenario: I have a source of truth document {In Progress Launches} and a column [CC Launch].

    Then destination sheet {Template} and a column [CC Launch].

    =INDEX({In Progress Launches Range 1}, MATCH([CC Launch]@row, {Template}, 0))

    This formula says I have a circular reference. Honestly, I'm a smart guy but I'm stumped and I need to resolve this asap.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @darrell.stevens

    Do either of your referenced columns contain a formula?

    {In Progress Launches Range 1} or  {Template}?

    If there's a formula in one of those columns and it errors in even just one cell, that error will then show up in any formula looking at those columns.

    Try wrapping the source sheet formulas in an IFERROR:

    =IFERROR(formula, "")

    Cheers,

    Genevieve

  • I got it! Nearly jumped out of my chair when the proper data came up.

  • Genevieve P.
    Genevieve P. Employee Admin

    Haha I'm glad to hear that! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!