+ Start a Discussion
Jordan FeiseJordan Feise 

How to create formula for week of the year from date/time field?

Hello, 
I am trying to create a field that displays week of the year that the lead was created based off the CreatedDate (Date/Time) Field.

I think I need to first convert the CreatedDate (Date/Time) to a (Date) field and then use a formula to change the date into a Week Number.

The output of the formula should be a number (between 0-52).

This is what I'm working with right now... but this formula has errors:

DAY( DATEVALUE( date/time ))
IF(
2
  CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) > 52,
3
  52,
4
  CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7)
5
)

Any help would be greatly appreciated! 
Best Answer chosen by Jordan Feise
Terence_ChiuTerence_Chiu
You need to add the actual field to formula.

IF( 

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

52, 

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

)








 

All Answers

Terence_ChiuTerence_Chiu
You need to add the actual field to formula.

IF( 

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

52, 

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

)








 
This was selected as the best answer
Jordan FeiseJordan Feise
Thank you!!! Easy fix :)
Ken BKen B
Does this take into account that 2015 has 53 weeks?
Patrick Solum 6Patrick Solum 6
I tried this (had to modify to work in a custom object) and got this error:  Compiled formula is too big to execute (19,954 characters). Maximum size is 5,000 characters
Davide GammoneDavide Gammone

I have post an Idea for that problem!

Formula Function ISOWEEKNUM

https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ


Thanks
Davide