You need to sign in to do that

Don't have an account?

SBforV

# Need help writing formula to display Week number, based on Date

I am a bit of a beginner at formulas, so hopefully one of you will think this is easy, and be able to help. :-)

Customer requirement:

Create a formula field that displays the Week Number (as Week 1, Week 2, etc), based on the Date entered on a particular record (same object).

Ex: 01/01/09 - 01/04/09 would return a value of "Week 1"

01/05/09 - 01/11/09 = Week 2

01/12/09 - 01/18/09 = Week 3

and so on.....

Is using the CASE function the way to go, and if so, what's the shortest way to create this formula?

Thank you in advance!

S

Jakester

Went to online help, typed "week" and the 4th result gives you what you need:

This formula calculates today’s day of the week as a number (0 = Sunday, 1 = Monday, 2 = Tuesday, and so on).

Similarly, this formula substitutes the TODAY() function shown in the previous example with a custom date field called Sign Up Date. It returns the day of the week as a number for that field.

SBforV

Thanks for trying to help, but I don't need day of the week, but Week.

They've got about 54 defined weeks in their custom fiscal calendar (for FY2009), and I they need to be able to report by those.

JakesterOops! You're right - my mistake.

thusker

I'm terrible with formulas, but I get a ton of help here. This is one that I use to give me a "Month Lead Created" for marketing to use in reports. Not sure if you can swap out Month for Day and then provide appropriate values like 1,"Week 1" etc. But thought I'd post it to see if it helped.

CASE(MONTH(datevalue(CreatedDate)) ,

1, "January",

2, "February",

3, "March",

4, "April",

5, "May",

6, "June",

7, "July",

8, "August",

9, "September",

10, "October",

11, "November",

12, "December", " ")

trublucould you advise if you figured this out please? Thanks in advance!!! MRietveld

Try this: MOD(FLOOR( (Date__c -DATEVALUE("2006-01-01" ))/7),52)+1

Replace Date__c by whatever date you want to have the weeknumber for.

This formula calculates the weeknumber of the closed date. It takes the 01/01/2006 date as the baseline because that date was a Sunday. This way I don't have to re-adjust for days. For the rest: I take the Floor of the delta divided by 7 to get the weeknumber, and then mod 52 to adjust for years.

Hope this helps.

Sugarfix72

I've just found your solution while searching and I just wanted to thank you as it works an absolute treat for what I need.

Cheers,

Darren.

kstites1This worked great! Thank you!

Brandy C Colmer

Thank you so much this is awesome Formula Wizardry. I am curious, how does the +1 come into play? Why do you need to add 1?

Also, how could I modify this to calculate the Week in the Quarter? Is that even possible?

ngabrani

To convert this into week number in current quarter, you just need to replace 52 by 13. That should work.

For a more detailed analysis of the formulat is present at this blog - http://astreait.com/wordpress/?p=48

Sapamrm

I tried using this formula and adjusted it so it calculates the weeknumber a case has been created.

MOD(FLOOR( ( DATEVALUE( CreatedDate ) -DATEVALUE(“2006-01-01″ ))/7),52)+1

I used DATEVALUE since CreatedDate is a Date/Time field.

The formula however did not work for me. I keep getting #Error!.

Any idea what went wrong?

Thanks.

Esther_Poulsen

This formula worked for me:

MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(2006,01,01))/7),52)+1

You can also change the day of the week that the week starts by modifying the formula this way:

MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(2006,01,01)-6)/7),52)+1

The -6 changes the week start date to Friday. You can adjust as needed. Hope this helps!

margotw

I'm using:

MOD(FLOOR((Expected_Launch_Date__c -DATEVALUE("2006-01-01"))/7),52)+1

Expected_Launch_Date__c is a custom field, data type = Date

No syntax errors.

But, on the page layout, the field shows #Error! in every case.

What am I missing?

Steve :-/What kind of a result are you looking for? #error usually means that there is a mathematic error, like you're dividing by 0, or multiplying by

blankor NULLHow is your formula field treating blank values? are they 0 or blank?

margotw

Formula Return Type = Number

I selected "Treat blank fields as zeroes." Should I try "Treat blank fields as blanks."?

The Expected Launch Date field is mandatory, so there shouldn't be any zeroes or blanks.

Steve :-/No, leave it as 0. Okay then there must be some other math error in there like Divide by 0 or something like that. Can you post a screenshot of the record with all of the fields and values? Either that or write out what the values are in the context of your Formula?

margotw

Not sure how to paste a screen shot, but it's:

Expected Launch Date 8/29/2011

Week # #Error!

If I edit the formula and substitute 8/29/2011 for Exepcted_Launch_Date__c, I get the following error:

Incorrect parameter for operator '-'. Expected Number, received Date

Steve :-/

Okay this is what I meant, we need yo break down your formula and find the match error. a #Erroe result basically means you're doing something that violates the laws of math.

MOD(FLOOR((Expected_Launch_Date__c -DATEVALUE("2006-01-01"))/7),52) + 1

so we need to go through your formula step by step and find the part that is wrong, like this:

1. 8/29/2011 - 1/1/2006 = X

2. X / 7 = Y

3. MOD(Y, 52) + 1 = Z

Esther_Poulsen

It depends on the data type of Expected_Launch_Date__c.

If it's a data type Date, then use this:

MOD(FLOOR( ( Expected_Launch_Date__c - DATE(2006,01,01)-6)/7),52)+1

If it's an internal CreatedDate type, then this formula should work:

MOD(FLOOR( ( DATEVALUE( Expected_Launch_Date__c ) - DATE(2006,01,01)-6)/7),52)+1

I've used both of these formulas with both of these data types. Also try changing the portion of the formula DATEVALUE("2006-01-01") to DATE(2006,01,01) and see if that works.

It's a little wacky but you can use DATEVALUE to convert a DATE datatype and then perform math with another DATE datatype, but you can't convert a string using DATEVALUE and then perform math functions with a DATE datatype. It took a few tries for me to get this working but I would try the formulas with changing the second part of the formula from DATEVALUE to DATE and see if that works.

margotw

Thanks for your help! I really appreciate it!

MOD(FLOOR( ( Expected_Launch_Date__c -DATEVALUE("2006-01-01" ))/7),52)+1 is now working!

I was trying to go through the step-by-step process you recommended.

When I copied the formula from Word to paste back into Salesforce, I got a syntax error on the DATEVALUE function. I think the quotes around 2006-01-01 came over in the wrong format (if that's possible)!?!? I deleted them and retyped them and the formula worked.

KnutzYes - Thank you!

pi r2

I ran into a number of syntax errors when I tried using the formulas shown in this thread. This is what worked for me:

MOD(FLOOR( ( DATEVALUE (CreatedDate) - DATEVALUE ("2012-01-01" ))/7),52)+1

However, now I have an additional question - this formula is based on 2012-01-01. That suggests to me that it is only useful for calculating the Week Number for Leads Created in 2012. Is there any way to make this formula work for Leads that were created years ago?

At the present time, I'm only using this field to report on New Leads Created in 2012 by Week Number. So it meets my needs, but I'm wondering what one would do if they wanted to calculate week number for leads created in 2009, 2010, 2011, and 2012.

hisalesforceDate today=System.today();

Date todaydate = System.today();

Date todaydateinstance = date.newinstance(todaydate.year(), todaydate.month(), todaydate.day());

Integer currentyear = todaydate.year();

Date startDate = date.newinstance(currentyear, 01, 01);

integer numberDaysDue = startDate.daysBetween(todaydateinstance);

Integer numberOfWeek = math.MOD(Integer.valueof(math.FLOOR( ( numberDaysDue )/7)),52)+1;

SimplySfdc

IF(MOD(FLOOR((DateValue(Execution_Time__c)-DATEVALUE("2007-01-01"))/7),52)=0,52,MOD(FLOOR((DateValue(Execution_Time__c)-DATEVALUE("2007-01-01"))/7),52))

Jakob Klok

I found this formula and wanted to use is. But I tested it, and got wrong results. For instance June 20th 2013 is week 25, but the formula returns 26.

Also December 30th, 2018 returns week 3 instead of 53.

So, for anyone who needs a formula to calculate the week number for a date, I believe this works:

FLOOR( (

Date__c - DATE( YEAR( Date__c),1,1) +

MOD( DATE(YEAR(Date__c),1,1) - DATE(1900, 1, 7), 7)

) / 7)

+ 1

Steve :-/

Try this =>

Jakob KlokThanks Stevemo, but isn't that the same function written slightly differently?

Or am I missing something?

ChickenOrBeefSteveMo, your formula works, but I need my weeks to start on Saturday instead of Sunday. Can your formula be amended for that?

Thanks!

ddennis

I tried the formula found in this post but the results were not always correct.

After some more digging I found this

Case(

MOD( DATE(YEAR(Actual_Close_Date__c ), 1, 1) - DATE (1900, 1, 7), 7),

0, ( Actual_Close_Date__c - DATE(YEAR(Actual_Close_Date__c ),1,1) + 3) /7,

1, ( Actual_Close_Date__c - DATE(YEAR(Actual_Close_Date__c ),1,1) + 4) /7,

2, ( Actual_Close_Date__c - DATE(YEAR(Actual_Close_Date__c ),1,1) + 5) /7,

3, ( Actual_Close_Date__c - DATE(YEAR(Actual_Close_Date__c ),1,1) + 6) /7,

4, ( Actual_Close_Date__c - DATE(YEAR(Actual_Close_Date__c ),1,1) + 7) /7,

5, ( Actual_Close_Date__c - DATE(YEAR(Actual_Close_Date__c ),1,1) + 1) /7,

6, ( Actual_Close_Date__c - DATE(YEAR(Actual_Close_Date__c ),1,1) + 2) /7,

99)

Agi

Hi,

to have the number of weeks of the date field in the quarter, you can use the following:

IF(

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

IF( Month(Date__c )<4, CEILING( ( Date__c - DATE( YEAR( Date__c ), 1, 1) + 1) / 7),

IF(AND( Month(Date__c ) >= 4,Month(Date__c ) < 7), CEILING( ( Date__c - DATE( YEAR( Date__c ), 4, 1) + 1) / 7),

IF(AND( Month(Date__c ) >= 7,Month(Date__c ) < 10), CEILING( ( Date__c - DATE( YEAR( Date__c ), 7, 1) + 1) / 7),

CEILING( ( Date__c - DATE( YEAR( Date__c ), 10, 1) + 1) / 7)))))

cdavis1.3975189747501807E12For anyone who may need it, here is the formula for a Saturday Start of the Week as the above will work for a Monday start of the work week.

Case(

MOD(DATE(YEAR(CloseDate),1,1)-DATE(1900,1,7),7),

0,(CloseDate-DATE(YEAR(CloseDate),1,1)+5)/7,

1,(CloseDate-DATE(YEAR(CloseDate),1,1)+6)/7,

2,(CloseDate-DATE(YEAR(CloseDate),1,1)+7)/7,

3,(CloseDate-DATE(YEAR(CloseDate),1,1)+8)/7,

4,(CloseDate-DATE(YEAR(CloseDate),1,1)+9)/7,

5,(CloseDate-DATE(YEAR(CloseDate),1,1)+10)/7,

6,(CloseDate-DATE(YEAR(CloseDate),1,1)+4)/7,

99)

CushtyThanks for this, What is the start of the fiscal year is febrary rather than january, how do I adjust the formula?

Ken BNone of these are working properly for years that have 53 weeks as per ISO-8601 week numbers:

Case(

http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php?year=2015

This formula (@ddennis) accounts for week 53:

MOD( DATE(YEAR(CloseDate ), 1, 1) - DATE (1900, 1, 7), 7),

0, ( CloseDate - DATE(YEAR(CloseDate ),1,1) + 3) /7,

1, ( CloseDate - DATE(YEAR(CloseDate ),1,1) + 4) /7,

2, ( CloseDate - DATE(YEAR(CloseDate ),1,1) + 5) /7,

3, ( CloseDate - DATE(YEAR(CloseDate ),1,1) + 6) /7,

4, ( CloseDate - DATE(YEAR(CloseDate ),1,1) + 7) /7,

5, ( CloseDate - DATE(YEAR(CloseDate ),1,1) + 1) /7,

6, ( CloseDate - DATE(YEAR(CloseDate ),1,1) + 2) /7,

99)

HOWEVER... last few days of Dec 2014 are numbered as Week 53, but should be Week 1

And the first few days of Jan 2016 are Week 0, but should be Week 53.

Has anyone found a solution for Week numbers starting on Monday that can properly account for years with 53 weeks?

Ken BI found what I was looking for here:

https://developer.salesforce.com/forums/?id=906F00000008ueCIAQ

Works perfectl for week numbers as per ISO-8601.

Junaid Khader

I think this could help you.

IF( CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) > 52, 52, CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) )

bhanu phi i got this.. no errors..

MOD(FLOOR((Created_Date__c - DATEVALUE( "2016-01-01 " ) ) / 7 ), 52) + 1

Mark GuildayLot's of formula's already posted but I needed one for the month number for weeks starting on Monday, so a monday-sunday week. I vetted it against a random sample of dates in 2016 and 2015, and passed anything I threw at it. So if anyone is still reading this thread and wants the formula, here you go:

FLOOR( (

Day_Actualized__c - DATE( YEAR( Day_Actualized__c),1,1) +

MOD( DATE(YEAR(Day_Actualized__c),1,1) - DATE(2007, 1, 1), 7)

) / 7)

+ 1

Thanks to everyone who got me started with this. Got to this by trial and error and a bit of logic. 1-1-2007 is a year that started on a Monday. It seems (don't quote me on this) that all you need to do is find a year that starts on the day of the week you need and then plug it in. Try it out for different start days and if that works post back here or if not let me know I'm wrong.

Is there an easy way to turn my formula (or if you have another one that works) into week of the quarter with weeks starting on Monday? I saw posts about this but it was for weeks that start on a Sunday and the logic was lost n me how to modify for my situation.

Shawn KurugantiFull Proof answer is below:

IF( CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) > 52, 52, CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) )

Harold CarlsonMark ~ Your formula is correct. A lot of incorrect formulas floating around including on Salesforce's own documentation. TeodoraFor me it seems like not even Mark's formula does the trick. For example, 2021-01-03 should be week 53 from 2020.

After long investigations, I have managed to wrap up a new formula which seems to do the trick:

TeodoraThis accounts for weeks that start on Monday. Jolene Scott-martin 9This works for us assuming you want the week to be Monday - Sunday. All the formulas I have seen use +1 instead of +2 but the calculations were out by a day, so the activitydate on a Monday was calculated as a week prior to the activitydate on Tuesday. I don't know if timezone matters - we are in the UK.

IF(

CEILING( (ActivityDate - DATE( YEAR(ActivityDate ), 1, 1)+2) / 7) > 52,52,

CEILING( (ActivityDate - DATE( YEAR(ActivityDate), 1, 1)+2) / 7)

)

Jennifer WilsonHello

I'm trying to get the Week Number into a new custom field. I need to calculate the week number from a standard Field Service Lightning field named "SchedStartTime". The data type for this field is Date/Time. I created a custom formula field to capture only the date from the SchedStartTime field named "Scheduled Start Date" with the formula: DATEVALUE(SchedStartTime ). I then created another custom field named "Week Number" with the formula: MOD (FLOOR (( Scheduled_Start_Date__c - DATE(2019,12,30) + (1/24) ) / 7),53)+1. However, there are errors when the Scheduled Start is midnight (00:00), the Week Number should update to the next week number but this isn't happening until the Scheduled Start is 01:00. See example below:

Scheduled Start Week Number14/06/2020 23:00 24

14/06/2020 23:30 24

15/06/2020 00:00 24

15/06/2020 00:30 24

15/06/2020 01:00 25

Can anyone provide help please? Note that our Org has the Default Time Zone(GMT+01:00) British Summer Time (Europe/London).

Joop Duijn 1This formula gives back the weeknumber in accordance with ISO-8601 and weeks starting on a Monday.

I've used 1-JAN-2018 as my reference year for a year starting on a Monday (if you have dates before this date pick an earlier year which starts on a Monday, for example 2007)It takes advantage of the fact that the 4th of January is always in Week 1 and December 28th is always in the last week of the year.

Obviously CloseDate is the Date we are considering to get a WeekNumber for.

If you want the year as well, you can use this formula to get the year:

Mikkel Damgaard LorensenThis was the old Excel way of doing it before "ISOWeek" was invented.

Works great in salesforce as well.

ElissandroI saw a lot of people considering dates like 2006-01-01 or 1900-01-01 but it's not made sense for me. For example, on Windows 10, there are 3 possible to get the Week Numbers:

1) First day of the year

2) First full week

3) First Four-Day week

I chose to use the full week on that year, for example, 12-31-2020 is week 53 of 2020, and 01-01-2021 is week 1 of 2021 (is on the same week, but changes the year).

Then I'd like to share with the community how I did it.

For 12-08-2020 the result is 2020/W50

For 01-01-2021 the result is 2021/W01

Formula:

I summed up the difference of weekday to get the days on the week completely. For example, the first day of 2020 is Wednesday and remains 3 days to complete the week, then I summed up with 7-4 = 3, the same for the last day of the year.

I hope that formula is useful for someone, regards,

Elissandro.

Davide GammoneI have post an Idea for that problem!

Formula Function ISOWEEKNUM (https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ"" target="_blank)https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ

Thanks

Davide

Tim Lomison 6My company had a slightly different need. They do not use ISO and instead Fiscal Week 1 starts on the first Sunday of the year. The dates prior to that in January should belong to the final Fiscal Week in December (52 or 53). A big thanks to @Teodora as that formula helped get me started. Posting our final formula here in case anyone stumbles across this and has the same requirements we had.

Jarvis HowardThis'll give a dynamic number without having to change the year in the formula: MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(YEAR(DATEVALUE(CreatedDate)),01,01))/7),52)+1

Rune Waage 2Hi SBforV

I hope you're still not waiting for an answer on this question, so this is mostly for people like me finding this thread searching for conversion from date to a week number.

Now, you can simply use the ISOWEEK-function which returns the ISO 8601-week number (from 1 through 53) for the given date, ensuring that the first week starts on a Monday.

Mark Deguisne 7

Using Jarvis Howard's formula, how would one account for if the Date field in question is truly just a Date field, and not a Date/Time field. The DATEVALUE formula is for date/time fields only and we are too late in the game to change the field type of what we're trying to reference in the formula. What I am looking to do is have the formula be dynamic so we don't have to change it each year (like Jarvis mentions). Here is the ideal solution of what we're looking for using Jarvis's formula:

MOD(FLOOR( ( DATEVALUE( Arrival_Date__c ) - DATE(YEAR(DATEVALUE(Arrival_Date__c)),01,01))/7),52)+1

But, I keep getting an "Error: Incorrect argument type for function 'DATEVALUE()'." error in the formula editor.