Formula referencing data is returning off by 1 week

Options
ShahA
ShahA
edited 12/09/19 in Formulas and Functions

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

Tags:

Comments

  • Eric M Oliveira
    Options

    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
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!