According to the documentation, TODAY()
returns the current date as a date data type.
To get the below function to work with a custom field, you should simply be able to modify it and change TODAY()
to the name of your custom field (Note: this field has to be of a Date
data type).
TEXT(MONTH(Custom_Field__c - MOD(Custom_Field__c - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY(Custom_Field__c - MOD(Custom_Field__c - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR(Custom_Field__c - MOD(Custom_Field__c - DATE(1900, 1, 7), 7)))
+ ' to ' +
TEXT(MONTH((Custom_Field__c + 7) - MOD(Custom_Field__c - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY((Custom_Field__c + 7) - MOD(Custom_Field__c - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR((Custom_Field__c + 7) - MOD(Custom_Field__c - DATE(1900, 1, 7), 7)))
First, &-.
matches all characters between &
and .
, including *
, (
, )
, '
, etc. Move the dash to either the beginning, the end, or escape it.
This behavior happens any time you have a character set where the pattern .-.
(where .
means any character) occurs, because that forms a character range (e.g. a-z
is a usual case, but it can actually be any two characters). This may cause some false positives/negatives.
Second, you can use the {}
operator to limit the number of appearances of stuff, and ()
groups to perform repeats to shorten your code. Your long form simply makes it harder to read.
Third, instead of using *
, I'd recommend using {,3}
if you want to limit the code to three characters per set.
Fourth, be aware that \w
also includes _
, so you might want to use the longer form of \w
minus the underscore.
This leads me to a final pattern of:
"([a-zA-Z0-9\\d&.\\/-]{,3}>){4}[a-zA-Z0-9&.\\/-]{,3}"
The rest of your formula looks okay, but you'll want to test this out to make sure. I wrote this as a validation rule in an object in my dev org, because I hate building processes just to test something like this, but it should work, at least in theory.
Best Answer
Here is a dev forum link on calculating the week no. The solution is for all years. Hope this helps.
Extracts from the link:
To be able to do weekly comparison report Year-to-Year with this weeknumbering it is necessary to create custom fields for the opportunity that calculates the Year and Week number for a given close date. Here is one solution using three custom formula fields.