Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Coral RacingCoral Racing 

Help Calculating Financial week in rolling years?

Hello.

I have used the following formula to work out what Financial Week a ticket was opened with our Support Desk using the following formula:
MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(2014,09,21)-6)/7),52)+1
Our 2014/15 Financial calendar started 2014-09-21 (Fri) which works great.   However what I need it to do is reset  back to 1 when we start the next Financial year 2015-09-25 and reset again 2016-09-23 and so on............

Any ideas?
 
K_McRaeK_McRae
Hi,

Is the first financial week of the year consistent every year e.g. is it say, always week 40?

If so, then this isn't too hard... I have done the same with financial months where April (month 4) is financial period 1.

If (month > 3) {
   financialPeriod = month - 3
} else {
   financialPeriod = month + 9
}

Keith

 
Coral RacingCoral Racing
Hi Keith Week 1 will always start on the Last Friday in September and each Period is 4 weekly starting on the last Friday in September, meaning there will always be 13 Financial Periods Is this still simple enough? Thanks Sonya
Coral RacingCoral Racing
Hi Keith

Do you mind explaining how your forula works above?  I am a little confused?

Thanks
Sonya