Calculating WEEKOFYEAR() for weeks that start on a day other than Sunday or Monday

[#1]

My company uses a fiscal calendar where the weeks start on Saturdays. According the the JSL WEEKOFYEAR() function we have two options: Rule 1 and 2 for weeks that start on Sundays, and Rule 3, for weeks that start on Mondays.

Has anyone created a JMP script to calculate the week of the year for weeks that start on a day other than Sunday or Monday?

[#2 · Accepted solution]

If you add a day to the calculation you will get what you want

Names Default To Here( 1 );
Week Of Year( Today() + In Days( 1 ) );

[#3]

Thank you txnelson!
I like your answer better than the one I came up with. Your answer is more efficient...

I have a table of dates, and used three separate formulas to add a day to the fiscal Week only if the day is Saturday:

In one column, Fiscal_Week, I use the WeekofYear() function: Week Of Year(:TIMESTAMP)
In the next column, DayOfWeek, I determine which day of the week it is: Day Of Week(:ET_TIME)
And in the third column, CompanyFiscalWeek I was able to get the correct value using the formula:
If (:Day of Week == 7, :Fiscal_Week + 1, :Fiscal_Week )

I'll use your solution instead. Thanks !
Jens

[#4]

I just noticed that the calculation appears to break down at the end of the year.

In the example for this year, 9/30/17 is the beginning of week 41.  All other weeks starting on Saturday properly increment up through 12/30/17 wich is the begining of week 54.

For some reason, rather than continuing with week 54 for the next 6 days, or skipping week 54 and starting week 1, JMP labels Saturday 12/30 as week 54, and Sunday 12/31 as the first day of Week 1 in 2018.  Looks like a flaw, since weeks should not start on two different days over a data set?

I will need to add an if loop to change all week 54 data to week 1.

[#5]

Good catch, here is a simple solution

Names Default To Here( 1 );
testdate = Date MDY( 12, 31, 2017 );
Week Of Year( testdate + If( Year( testdate ) == Year( testdate + In Days( 1 ) ), In Days( 1 ), 0 ) );

[#6 · Accepted solution]

Sorry the delay in replying. Your solution works well. Thank you.

Just to make sure that the solution is clear,

---

@txnelson wrote:

Good catch, here is a simple solution

Names Default To Here( 1 );
testdate = Date MDY( 12, 31, 2017 );
Week Of Year( testdate + If( Year( testdate ) == Year( testdate + In Days( 1 ) ), In Days( 1 ), 0 ) );

---

by adding 1 day to testdate with the In Days(1) function, Sunday = 2, Monday = 3, ... Friday = 7 and Saturday wraps around to be 1 again since there is no 8th day in a week.

I also like that you can define a variable within a formula. Very helpful!

[#7]

Besides different days for "first of week", it's also a bit surprising, how Week of Year is calculated in general.

Especially when comparing "Week of Year" and "Year Week" (from the new formula context menu).
In 2023, Rule #3 was more than 1 week delayed compared to Rule #1:

New Table( "Week",
	Add Rows( 30 ),
	New Column( "date",
		Format( "m/d/y", 12 ),
		Formula( Date MDY( 1, 1, 2023 ) + (Row() - 11) * (24 * 3600) )
	),
	New Column( "\!"Year Week\!"",
		Character,
		"Nominal",
		Formula(
			Local(
				{w = Week Of Year( :date, 3 ), y = Year( :date ), m = Month( :date )
				},
				If( Is Missing( :date ),
					"",
					Char( If( m / w >= 12, y + 1, w / m >= 52, y - 1, y ) ) ||
					If( w >= 10, "W", "W0" ) || Char( w )
				)
			)
		)
	),
	New Column( "Week of Year, rule1",
		Formula( Week Of Year( :date ) )
	),
	New Column( "Week of Year, rule3",
		Format( "Best", 12 ),
		Formula( Week Of Year( :date, 3 ) ),
		Color Cells( {32, {19, 20, 21, 22, 23, 24, 25}} )
	),
	New Column( "day",
		Formula( Day Of Week( :date ) ),
		Value Labels( {1 = "Sun", 2 = "Mon"} ),
		Use Value Labels( 1 )
	)
)