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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I got it! Nearly jumped out of my chair when the proper data came up.
-
Haha I'm glad to hear that! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!