Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BuellBuell 

Work Week Number Within a Year Date Calculation

Someone asked for help on figuring out a formula to calculate what work week a date falls within, with work weeks starting on Mondays, and I thought I'd share.

 

 

((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0, 
6, - 1, 
2, + 2, 
4, + 4, 
5, + 5, 
1, + 1, 
+ 3))) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0, 
6, - 1, 
2, + 2, 
4, + 4, 
5, + 5, 
1, + 1, 
+ 3))), 7) = 0, 0, 1)
 
 Just change CloseDate to the date field in question.
Message Edited by Buell on  08-20-2009 04:39 PM
Message Edited by Buell on  08-20-2009 04:43 PM


J Roge.ax1108J Roge.ax1108
what would you have to edit to get this to work for the start date being 03/072011 (3rd of july 2001) is it possible?
 
Thanks
 
J


J Roge.ax1108J Roge.ax1108
Here is my solution that works in case anyone else was interested, works great :)
 
MOD(FLOOR((Last_Modified_Date__c - DATE(YEAR(Last_Modified_Date__c),1,1))/7) + 26,52)+1



SimplySfdcSimplySfdc
I tried your formula is not working right, try this one seems more accurate:


 


(MOD(FLOOR((DateValue(Execution_Time__c)-7-DATEVALUE("2007-01-01"))/7),52))+1


 


Sherry Culbertson 1Sherry Culbertson 1
Buell,
This worked wonderfully!  Thank you for sharing.


Tenacious BenTenacious Ben
Rather late to the party, but I ran into this some time ago and ended up with the following formula after many revisions.  This uses ISO weekday numbers (https://en.wikipedia.org/wiki/ISO_week_date) which means Monday=1 and Sunday=7 (rather than starting on Sunday).  Replace "
your_date_field__c" with the date field you are testing.

FLOOR(
  (
    (your_date_field__c - DATE(YEAR(your_date_field__c), 1, 1) + 1) - 
    (IF
      (MOD (your_date_field__c - DATE(1984, 1, 1), 7) = 0, 7, MOD(your_date_field__c - DATE(1984, 1, 1), 7) )
    ) + 10
  ) / 7
)


 


Laura Walker 7Laura Walker 7
Depending on what you need to use the data for you could use fiscal years where it is all just added in in one go and then you get the benefit of the reporting. You can start the year on any day that you choose.