# T-minus dates when going over to next year

Options

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?

• Employee
edited 11/16/22
Options

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) + ""

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

October 8 - 10, Seattle, WA | Register now

• Options

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.

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Options

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

• Employee
Options

Haha I'm glad to hear that! 🙂