Has anyone written a column formula or script that will allow me to convert a date:time to a particular week or quarter in my company's OBSCURE fiscal calendar, known as the "5-4-4" fiscal calendar? I see google references to other fiscal calendars, but apparently the 5-4-4 is uncommon.
From what I understand of that system you could just find the number of days from the start of a given year and then calculate the week and quarter based on the number of days and length of each quarter. You might need to take into account 53 week years, but this should get you started:
New Table( "Fiscal Week",
Add Rows( 1000 ),
New Column( "Date", Numeric, "Continuous", Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( Date DMY( 4, 1, 2021 ) + Row() * In Hours( 12 ) )
),
New Column( "Fiscal Year", Numeric, "Continuous", Format( "Best", 12 ),
Formula(
Floor( (:Date - Date DMY( 4, 1, 2021 )) / In Days( 52 * 7 ) ) + 2021
)
),
New Column( "Fiscal Day of Year", Numeric, "Continuous", Format( "Best", 12 ),
Formula(
Floor( Mod( (:Date - Date DMY( 4, 1, 2021 )) / In Days( 1 ), 52 * 7 ) )
+ 1
),
Set Display Width( 67 )
),
New Column( "Fiscal Week of Year", Numeric, "Continuous", Format( "Best", 12 ),
Formula( Floor( (:Fiscal Day of Year - 1) / 7 ) + 1 ),
Set Display Width( 85 )
),
New Column( "Fiscal Quarter", Numeric, "Continuous", Format( "Best", 12 ),
Formula( Floor( (:Fiscal Week of Year - 1) / 13 ) + 1 ),
Set Display Width( 65 )
)
);That should get me until a 53 week year, and then it won't matter because the Armageddon will start. :> Thanks
