Formula to pull in fiscal work week is incorrect

Adriane Price

I am continually getting the incorrect work week. In the below screen shot the Onboarded Week should be Week 16. Attached is our lookup sheet from smartsheet. Our fiscal week starts on Saturday, January 29, 2022 and the fiscal week ends on Friday, February 4, 2022. the pattern is 4,4,5 (kinda) you can see it gets a little particular towards the end of 2022 (FY2023).

I have attempted variations by replacing the -5 with (-4,-6,-3) and the +48 to +52. Beyond that, I am lost. I do not know what I am doing or how to adjust the formula to reflect our fiscal weeks to adjust according to the excel/PDF sheet attached.

Fiscal Calendar FY23.pdf

WW Date Lookup.xlsx

Formula:

=IFERROR(IF((WEEKNUMBER([Onboarded Date]@row) - 5) < 0, WEEKNUMBER([Onboarded Date]@row) + 48, WEEKNUMBER([Onboarded Date]@row) - 5), "")

Toufong Vang

Here's the really ugly formula...

= IF(( IF(WEEKDAY([Onboarded Date]@row) = 7, (IF(WEEKNUMBER([Onboarded Date]@row) <= 3, WEEKNUMBER([Onboarded Date]@row) + 52, WEEKNUMBER([Onboarded Date]@row))) - 3, (IF(WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) <= 3, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) + 52, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row))) - 4) ) = 0, 52, ( IF(WEEKDAY([Onboarded Date]@row) = 7, (IF(WEEKNUMBER([Onboarded Date]@row) <= 3, WEEKNUMBER([Onboarded Date]@row) + 52, WEEKNUMBER([Onboarded Date]@row))) - 3, (IF(WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) <= 3, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) + 52, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row))) - 4) ))

Here's the explanation...

Your organization's Work Week WW has a 3 week offset from Smartsheet's WEEKNUMBER(), WK#.

So... when the result of the WK# expression is less than or equal to 3, add 52; otherwise, leave it be.

IF( (__) <=3, (__) + 52, (__) )

The original formula has two WEEKNUMBER() expressions:

We need to apply the condition above to each of them.

The first expression, WEEKNUMBER([Onboarded Date]@row), ends up as...

IF( (WEEKNUMBER([Onboarded Date]@row)) <=3, (WEEKNUMBER([Onboarded Date]@row)) + 52, (WEEKNUMBER([Onboarded Date]@row)) )

The second expression, 

WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)

ends up as...

IF( (WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)) <=3, (WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)) + 52, (WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)) )

...plug the two back into the original formula...

=IF(WEEKDAY([Onboarded Date]@row) = 7, ( ____ ) - 3, ( ____ )- 4)

...and the result is...

However, when the date of the 52nd WW is not a Saturday, the formula evaluates to "0". Resolve this by forcing it to "52".

=IF( (__) = 0, 52, (__) )

So it ends up looking like this...

Toufong Vang

Try...

= IF( WEEKDAY([Onboarded Date]@row) = 7, WEEKNUMBER([Onboarded Date]@row) - 3, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) - 4)

EXPLANATION

Your 1st work week starts on 01/29/2022. However, Smartsheet WEEKNUMBER( ) returns "4" for that week, WEEKNUMBER() of "01/29/2022".

So to arrive at WW = 1 you need to subtract "3" from WEEKNUMBER() of "01/29/2022".

In other words, when the "Onboarded Date" falls on a Saturday, the correct WW is WEEKNUMBER([Onboarded Date]@row) - 3.

The problem for using this expression alone to calculate your WW occurs when the "Onboarded Date" falls on a day other than Saturday.

Intuitively, you know when that occurs, you only have to look at the WW of the upcoming Saturday and subtract 1 extra week, "-4", instead of "-3".

The challenge is figuring out the date is for the upcoming Saturday. For example, when Onboarded Date = 05/12/2022 , what is the date of the upcoming Saturday?

To find this, use the help of the function WEEKDAY(). It returns the day of the week for a date.

Using WEEKDAY() for 05/12/2022 returns "5".

Subtract "5" from "7" (Saturday) which equals "2". 7 - WEEKDAY([Onboarded Date]@row)

Adding that number of days, "2", to the Onboarded Date of 05/12/2022 gives you the date of the upcoming Saturday, 05/14/2022. 7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row

Now, when you take the WEEKNUMBER() of 05/14/2022, and subtract "4" from it, you arrive at the WW of 05/12/2022 which is 15. WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) - 4

Adriane Price

@Toufong Vang - okay that makes sense, would or should that be working for previous years? for example, I have one here that should have been the fiscal year 2022 however it pulls in 2023 and -1 for the workweek. Technically it should be WW 52.

Toufong Vang

Here's the really ugly formula...

= IF(( IF(WEEKDAY([Onboarded Date]@row) = 7, (IF(WEEKNUMBER([Onboarded Date]@row) <= 3, WEEKNUMBER([Onboarded Date]@row) + 52, WEEKNUMBER([Onboarded Date]@row))) - 3, (IF(WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) <= 3, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) + 52, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row))) - 4) ) = 0, 52, ( IF(WEEKDAY([Onboarded Date]@row) = 7, (IF(WEEKNUMBER([Onboarded Date]@row) <= 3, WEEKNUMBER([Onboarded Date]@row) + 52, WEEKNUMBER([Onboarded Date]@row))) - 3, (IF(WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) <= 3, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row) + 52, WEEKNUMBER(7 - WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row))) - 4) ))

Here's the explanation...

Your organization's Work Week WW has a 3 week offset from Smartsheet's WEEKNUMBER(), WK#.

So... when the result of the WK# expression is less than or equal to 3, add 52; otherwise, leave it be.

IF( (__) <=3, (__) + 52, (__) )

The original formula has two WEEKNUMBER() expressions:

We need to apply the condition above to each of them.

The first expression, WEEKNUMBER([Onboarded Date]@row), ends up as...

IF( (WEEKNUMBER([Onboarded Date]@row)) <=3, (WEEKNUMBER([Onboarded Date]@row)) + 52, (WEEKNUMBER([Onboarded Date]@row)) )

The second expression, 

WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)

ends up as...

IF( (WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)) <=3, (WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)) + 52, (WEEKNUMBER(7-WEEKDAY([Onboarded Date]@row) + [Onboarded Date]@row)) )

...plug the two back into the original formula...

=IF(WEEKDAY([Onboarded Date]@row) = 7, ( ____ ) - 3, ( ____ )- 4)

...and the result is...

However, when the date of the 52nd WW is not a Saturday, the formula evaluates to "0". Resolve this by forcing it to "52".

=IF( (__) = 0, 52, (__) )

So it ends up looking like this...

Adriane Price

Oh my yes, that isn't pleasant, definitely way over my head. Thank you for the breakdown, I would not have been able to get here without your guidance. The breakdown did help me understand.

One last question; is there a way to make sure that if there is no date in the "onboarded date" column that it shows blank instead of #INVALID DATA?