Apex Code Development You need to sign in to do that
Don't have an account?
Simonas Paulaitis How to create formula for Created Date Week Number?
Hi Guys.
I am trying to have a formula field for the Week Number of the year. So, if opportunity is created on the 3rd January 2016, it would show "Week 1 2016".
I use the report in the Matrix format.
Currently, the field I am using for week number calculation is called "Created Date"
Format of the field is: DD/MM/YYYY (this was changed in the regional settings from the american MM/DD/YYYY format)
I have browsed forums and found many formulas... which none of them work.
One of them, for example:
MOD(FLOOR((Created_Date__c - DATEVALUE( "2016-01-01 " ) ) / 7 ), 52) + 1
It gives me the following error:
Error: Invalid custom summary formula definition: Field Created_Date__c does not exist. Check spelling.
Now, I am new in SFDC formulas, so as far as I understand, there is no such field. It should be called Crated Date. But if i use just Created Date - I get error that there is no field Created. If I use formula like this:
MOD(FLOOR(("Created Date" - DATEVALUE( "2016-01-01 " ) ) / 7 ), 52) + 1
I get error: Error: Invalid custom summary formula definition: Incorrect parameter type for operator '-'. Expected Number, Date, DateTime, received Text.
I know that I am doing mistake somewhere, but where?
Thank you for the answers!
There are two problems with you formula as far as I can tell at the moment.
- The field you are trying to reference for the created date is a standard field so is referenced as CreatedDate
- As created date is a DateTime field, you either need to convert it to a date or convert your date to a datetime
Try using either: orTo resolve your error messages
Try this.
This formula worked for me: You can also change the day of the week that the week starts by modifying the formula this way: The -6 changes the week start date to Friday. You can adjust as needed. Hope this helps!
As created date is a standard field you have to refer it as this way CreatedDate ,which should probably resolve your issue.
Kindly mark this post as solved if the information help's so that it gets removed from the unanswered queue which results in helping others who are really in need of it.
Best Regards,
Nagendra.P
many thanks for the help. I tried all the variants proposed here with the following errors:
MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(2016,01,01))/7),52)+1
Error: Invalid custom summary formula definition: Field CreatedDate does not exist. Check spelling.
MOD(FLOOR(( DATEVALUE(CreatedDate) - DATEVALUE( "2016-01-01 " ) ) / 7 ), 52) + 1)
Error: Invalid custom summary formula definition: Syntax error. Extra ')'.
MOD(FLOOR(( DATEVALUE(CreatedDate) - DATEVALUE( "2016-01-01 " ) ) / 7 ), 52) + 1
Error: Invalid custom summary formula definition: Field CreatedDate does not exist. Check spelling.
MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(2016,01,01)-6)/7),52)+1
Error: Invalid custom summary formula definition: Field CreatedDate does not exist. Check spelling.
It is interesting that if I look at the Summary Fields section, just above the Formula entering field, it lists many various fields which I could use, and Created Date is not there...
I tried to group the report by the Created date, using the Fiscal Week.
This gives me the week number, but it makes some sort of error.
It tells me, that Opportunity is created on the Week 51, while actually it is created on 1st of November. Which by no means can be week 51 (or week 57 some times).
Where's the problem? :(
Custom formulas on reports are not very helpful, as you can only use number fields.
I would suggest you add it as a custom field on the object you are reporting on, if you can, then you can group it on the report.
Here's additional:
Created Date: 12/01/2016 <- OK
If Grouped by Calendar Week, it is: 11/01/2016 - 17/01/2016 <- OK
If grouped by Fiscal Week: Week 3 FY 2016 <- OK
Well it looks right.
Then:
Created date: 01/11/2016 <-OK
Grouped by Calendar Week: 31/10/2016 - 06/11/2016 <- OK
Grouped by Fiscal week: Week 53 FY 2016 <- mmmm WHAT?!
Where's the problem?
In this case - How to add the formula field to the opportunity?
Formula Function ISOWEEKNUM
https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ
Thanks
Davide