Ask Search:
Crystal GoodaleCrystal Goodale 

How do I incorporate Year into custom week number formula field?

Here is my custom week number formula.  How do I adjust it to incorporate the year?
Example: 201901

User-added image
Best Answer chosen by Crystal Goodale
Steve MolisSteve Molis
Try this
TEXT(YEAR(DATEVALUE(CreatedDate))) +
LPAD(
TEXT(
IF( 

CEILING( ( DATEVALUE(CreatedDate) - DATE( YEAR( DATEVALUE(CreatedDate) ), 01, 01) + 1) / 7) > 52, 

52, 

CEILING( ( DATEVALUE(CreatedDate) - DATE( YEAR( DATEVALUE(CreatedDate) ), 01, 01) + 1) / 7) 

)), 2, "0")

 

All Answers

Deepak AnandDeepak Anand
Like this = 
(YEAR(DATEVALUE(CreatedDate)) * 100) + 
IF(
    CEILING((DATEVALUE(CreatedDate) - DATE(YEAR(DATEVALUE(CreatedDate)), 01, 01) + 1) / 7) > 52,
    52,
    CEILING((DATEVALUE(CreatedDate) - DATE(YEAR(DATEVALUE(CreatedDate)), 01, 01) + 1) / 7)
)
Steve MolisSteve Molis
Can you elaborate on how you're trying to "incorporate the year?" 

Are you looking for a result like this?
 
TEXT(YEAR(DATEVALUE(CreatedDate))) +" - "+
TEXT(
IF( 
  CEILING( ( DATEVALUE(CreatedDate) - DATE( YEAR( DATEVALUE(CreatedDate) ), 01, 01) + 1) / 7) > 52,
  52,
  CEILING( ( DATEVALUE(CreatedDate) - DATE( YEAR( DATEVALUE(CreatedDate) ), 01, 01) + 1) / 7)
)
)

 
Crystal GoodaleCrystal Goodale
Current result is just a number....1, 2, 3....

I would like the year to be at the beginning followed by the week....201901
Steve MolisSteve Molis
Here's one I use on my Dev org 
TEXT(YEAR( Date_1__c ))+" - "+ 
LPAD(
TEXT(
IF( 

CEILING( ( Date_1__c - DATE( YEAR( Date_1__c ), 01, 01) + 1) / 7) > 52, 

52, 

CEILING( ( Date_1__c - DATE( YEAR( Date_1__c ), 01, 01) + 1) / 7) 

)), 2, "0")
You'll probably want to LPAD the Week Number of weeks 1 - 9
 
Steve MolisSteve Molis
Try this
TEXT(YEAR(DATEVALUE(CreatedDate))) +
LPAD(
TEXT(
IF( 

CEILING( ( DATEVALUE(CreatedDate) - DATE( YEAR( DATEVALUE(CreatedDate) ), 01, 01) + 1) / 7) > 52, 

52, 

CEILING( ( DATEVALUE(CreatedDate) - DATE( YEAR( DATEVALUE(CreatedDate) ), 01, 01) + 1) / 7) 

)), 2, "0")

 
This was selected as the best answer
Crystal GoodaleCrystal Goodale
Perfect - THANK YOU!!