Apex Code Development 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
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.
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.
Oops! You're right - my mistake.
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", " ")
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.
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.
This worked great! Thank you!
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?
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
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.
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!
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?
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?
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.
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?
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
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
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.
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.
Yes - Thank you!
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.
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;
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))
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
Try this =>
Thanks Stevemo, but isn't that the same function written slightly differently?
Or am I missing something?
SteveMo, your formula works, but I need my weeks to start on Saturday instead of Sunday. Can your formula be amended for that?
Thanks!
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)
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)))))
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)
What is the start of the fiscal year is febrary rather than january, how do I adjust the formula?
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?
I found what I was looking for here:
https://developer.salesforce.com/forums/?id=906F00000008ueCIAQ
Works perfectl for week numbers as per ISO-8601.
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) )
i got this.. no errors..
MOD(FLOOR((Created_Date__c - DATEVALUE( "2016-01-01 " ) ) / 7 ), 52) + 1
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.
IF( CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) > 52, 52, CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) )
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) ) )IF(
CEILING( (ActivityDate - DATE( YEAR(ActivityDate ), 1, 1)+2) / 7) > 52,52,
CEILING( (ActivityDate - DATE( YEAR(ActivityDate), 1, 1)+2) / 7)
)
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 Number
14/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).
IF( AND( CloseDate < DATE(Year(CloseDate),1,4), MOD(MOD(DATE(Year(CloseDate),1,4)-DATE(2018,1,1),7),7)-(DATE(Year(CloseDate),1,4)-CloseDate)<0 ), FLOOR((CloseDate-DATE(Year(CloseDate)-1,1,4)+MOD(MOD(DATE(Year(CloseDate)-1,1,4)-DATE(2018,1,1),7),7))/7)+1 , IF( AND( CloseDate > DATE(Year(CloseDate),12,28), MOD(MOD(DATE(Year(CloseDate),12,28)-DATE(2018,1,1),7),7)+(CloseDate-DATE(Year(CloseDate),12,28))>6 ), 1, FLOOR((CloseDate-DATE(Year(CloseDate),1,4)+MOD(MOD(DATE(Year(CloseDate),1,4)-DATE(2018,1,1),7),7))/7)+1 ) )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:
IF( AND( CloseDate < DATE(Year(CloseDate),1,4), MOD(MOD(DATE(Year(CloseDate),1,4)-DATE(2018,1,1),7),7)-(DATE(Year(CloseDate),1,4)-CloseDate)<0 ), Year(CloseDate)-1, IF( AND( CloseDate > DATE(Year(CloseDate),12,28), MOD(MOD(DATE(Year(CloseDate),12,28)-DATE(2018,1,1),7),7)+(CloseDate-DATE(Year(CloseDate),12,28))>6 ), Year(CloseDate)+1, Year(CloseDate) ) )Works great in salesforce as well.
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:
TEXT(YEAR(DATEVALUE(CreatedDate))) & '/W' & RIGHT( '0' & TEXT( FLOOR( MOD( ( DATEVALUE(CreatedDate) - DATEVALUE( TEXT(YEAR(DATEVALUE(CreatedDate)))&'-01-01' ) + 7-WEEKDAY(DATEVALUE( TEXT(YEAR(DATEVALUE(CreatedDate)))&'-01-01' )) ) /7 , ( DATEVALUE( TEXT(YEAR(DATEVALUE(CreatedDate)))&'-12-31' ) - DATEVALUE( TEXT(YEAR(DATEVALUE(CreatedDate)))&'-01-01' ) + ( 7-WEEKDAY(DATEVALUE( TEXT(YEAR(DATEVALUE(CreatedDate)))&'-01-01' )) ) + ( 7-WEEKDAY(DATEVALUE( TEXT(YEAR(DATEVALUE(CreatedDate)))&'-12-31' )) ) + + 1 ) / 7 ) + 1 ) ), 2)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.
Formula Function ISOWEEKNUM (https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ"" target="_blank)
https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ
Thanks
Davide
IF( /* Jan dates that need to be counted against the previous year */ AND( MONTH(Date__c) = 1, MOD(Date__c - DATE(1900, 1, 7), 7 ) >= DAY(Date__c) ), TEXT(YEAR(Date__c) - 1) + "-" + /* Week can be either 52 or 53. */ TEXT( IF( OR( MOD( Date(YEAR(Date__c)-1, 1, 1) - DATE( 1900, 1, 7), 7 ) = 0, 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, 7), 7 ) = 6 ) ) , 53, 52) ), /* 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(DATEVALUE(TEXT(YEAR(Date__c))&"-01-01") - DATE(1900, 1, 7), 7), 0, DATEVALUE(TEXT(YEAR(Date__c))&"-01-01"), 1, DATEVALUE(TEXT(YEAR(Date__c))&"-01-07"), 2, DATEVALUE(TEXT(YEAR(Date__c))&"-01-06"), 3, DATEVALUE(TEXT(YEAR(Date__c))&"-01-05"), 4, DATEVALUE(TEXT(YEAR(Date__c))&"-01-04"), 5, DATEVALUE(TEXT(YEAR(Date__c))&"-01-03"), DATEVALUE(TEXT(YEAR(Date__c))&"-01-02") ) )/7),53)+1) , 2) )MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(YEAR(DATEVALUE(CreatedDate)),01,01))/7),52)+1
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.
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.