function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Jordan 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_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_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 Feise
Thank you!!! Easy fix :)
Ken B
Does this take into account that 2015 has 53 weeks?
Patrick 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 Gammone

I have post an Idea for that problem!

Formula Function ISOWEEKNUM

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

Thanks
Davide