+ Start a Discussion
SBforVSBforV 

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

 

JakesterJakester

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).

MOD(TODAY() - DATE(1900, 1, 7), 7)

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.

MOD(Sign_Up_Date__c - DATE(1900, 1, 7), 7)

 

SBforVSBforV

 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.

 

JakesterJakester

Oops! You're right - my mistake.

thuskerthusker

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", " ")

trublutrublu
could you advise if you figured this out please? Thanks in advance!!!
MRietveldMRietveld

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.  

Sugarfix72Sugarfix72

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.

kstites1kstites1

This worked great!  Thank you!

Brandy C ColmerBrandy 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?

ngabraningabrani

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

SapamrmSapamrm

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_PoulsenEsther_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!

margotwmargotw

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 MolisSteve Molis

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 blank or NULL  

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

margotwmargotw

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 MolisSteve Molis

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?

margotwmargotw

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 MolisSteve Molis

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_PoulsenEsther_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.

margotwmargotw

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.

 

KnutzKnutz

Yes - Thank you!

pi r2pi 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.

hisalesforcehisalesforce

Date 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;

SimplySfdcSimplySfdc

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 KlokJakob 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 MolisSteve Molis

Try this =>

 

CEILING(((Testing_Date__c - DATE(YEAR(Testing_Date__c), 1, 1) + 1) +
MOD(DATE(YEAR(Testing_Date__c), 1, 1) - DATE(1900, 1, 7), 7)) / 7) 

 

Jakob KlokJakob Klok

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

Or am I missing something?

ChickenOrBeefChickenOrBeef

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

Thanks!

ddennisddennis

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)

AgiAgi

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.3975189747501807E12cdavis1.3975189747501807E12
For 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)
CushtyCushty
Thanks for this,
What is the start of the fiscal year is febrary rather than january, how do I adjust the formula?
Ken BKen B
None of these are working properly for years that have 53 weeks as per ISO-8601 week numbers:
http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php?year=2015

This formula (@ddennis) accounts for week 53:
Case(
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 BKen B

I found what I was looking for here:
https://developer.salesforce.com/forums/?id=906F00000008ueCIAQ

Works perfectl for week numbers as per ISO-8601.

Junaid KhaderJunaid 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 pbhanu p
hi 
 i got this.. no errors..
MOD(FLOOR((Created_Date__c - DATEVALUE( "2016-01-01 " ) ) / 7 ), 52) + 1
Mark GuildayMark Guilday
Lot'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 KurugantiShawn Kuruganti
Full 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 CarlsonHarold Carlson
Mark ~ Your formula is correct.  A lot of incorrect formulas floating around including on Salesforce's own documentation.
TeodoraTeodora
For 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:
/* The first week of the year is the week containing the first Thursday of the year or the first week containing Jan 4th */
IF(
    /* Jan dates that need to be counted against the previous year */
    AND(
		MONTH(Date__c) = 1,
		AND(
			MOD( Date__c - DATE( 1900, 1, 8 ), 7 ) > DAY(Date__c) ,
			MOD( Date(YEAR(Date__c), 1, 4) - DATE( 1900, 1, 7 ), 7 ) != 6
		)	
    ),
	TEXT(YEAR(Date__c) - 1) + "-" + 
    /* Week can be either 52 or 53. If the Year starts on a Thursday or is a leap year that starts on a Wednesday, that particular year will have 53 numbered weeks. */
	TEXT(
		IF(
			OR(
				AND(
					OR( 
					  MOD( YEAR( Date__c )-1, 400 ) = 0, 
					  AND( 
					   MOD( YEAR( Date__c )-1, 4 ) = 0,
						MOD( YEAR( Date__c )-1, 100 ) != 0
					  )
					),
					MOD( Date(YEAR(Date__c)-1, 1, 1) - DATE( 1900, 1, 8 ), 7 ) = 2	
				),
				MOD( Date(YEAR(Date__c)-1, 1, 1) - DATE( 1900, 1, 8 ), 7 ) = 3
			)
		, 53, 52)
  ),
	  IF(
		/* Dec dates that need to be counted against the next year */
		AND(
			MONTH(Date__c) = 12,
			OR(
				AND(
					MOD( Date(YEAR(Date__c), 1, 1) - DATE( 1900, 1, 8 ), 7 ) <= Date__c - DATE(YEAR(Date__c), 12,29),
					MOD(MOD( Date(YEAR(Date__c) + 1, 1, 4) - DATE( 1900, 1, 7 ), 7 ) - 1, 7) = 6
				),
				AND(
					MOD( Date__c - DATE( 1900, 1, 8 ), 7 ) < Date__c - DATE(YEAR(Date__c), 12,29),
					MOD(MOD( Date(YEAR(Date__c) + 1, 1, 4) - DATE( 1900, 1, 7 ), 7 ) - 1, 7) != 6
				)
			)		
		), 
		TEXT(YEAR(Date__c) + 1) + "-01",
		/* Regular calculation, with a buffer according to the first day of Week 1*/
		Text(YEAR(Date__c)) + "-" +
		RIGHT("0" +	
			 TEXT(MOD(FLOOR((Date__c + 
				CASE(MOD( Date(YEAR(Date__c), 1, 1) - DATE( 1900, 1, 8 ), 7 ),
					0, 0,
					1, 1,
					2, 2,
					3, 3,
					4, -3,
					5, -2,
					-1)
			- DATEVALUE(TEXT(YEAR(Date__c)) + "-01-01" ))/7),53)+1)
		 , 2)
	  )
	
)

 
TeodoraTeodora
This accounts for weeks that start on Monday.
Jolene Scott-martin 9Jolene Scott-martin 9
This 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) 
)