T-minus dates when going over to next year

darrell.stevens

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?

Genevieve P.

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

darrell.stevens

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.

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

darrell.stevens

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

Genevieve P.

Haha I'm glad to hear that! 🙂