Formula referencing data is returning off by 1 week

ShahA

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

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