Formula referencing data is returning off by 1 week

Hey there,

I'm currently using the below formula to pull in and report the availability of our consultants on a per week basis. The formula sits in a Smartsheet that references another Smartsheet where the data is in two columns from a start date to an end date every week that Consultant is booked. The Smartsheet utilizing the formula displays a nice single number out of 40 per week. This number is the hours the Consultant is available that week.

The issue that's arising is that in the transition from the last week of 2020 to the first week of 2021 the data becomes off by 1 week. Not sure what is going on or if I did the formula incorrect. However, the data prior to 2021 is pulling correctly.

Below are screenshots and also plaintext of formula below.

=40 - (SUMIFS({Forecasts (SS) Hours}, {Forecasts (SS) Consultant}, $[Consultant Name]25, {Forecasts (SS) Start}, WEEKNUMBER(@cell) = 1, {Forecasts (SS) Cancelled}, 0, {Forecasts (SS) Start}, YEAR(@cell) = 2021))

Data off by one week.jpg

Data that is being referenced.jpg

Find more posts tagged with

Formulas

Comments

Eric M Oliveira

Hello,

Happy to help investigate why you're experiencing an undesired result when utilizing formulas.

Note: If you require Smartsheet technical support it is best to connect with us via the 'contact us' link instead of the community to ensure a speedy response. You can contact us at support here: https://help.smartsheet.com/contact

In order for me to further investigate please provide an image of the cell that is producing the undesired result and what it should be, based on your columns. Also, please provide the column names to ensure we know which columns the formula is referencing.

We could also do a screen share session if you'd like to jump on a call to resolve this issue. Please click here.

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Eric M Oliveira

Hello,

It was my pleasure chatting with you today! At the end of our chat, we were able to discover the reason December 28 2020 to January 1st 2021 is a leap year and considered week number 53. Adjusting the sheet accordingly resolved the issue.

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?