Calendar Week Date Range as a formula field

Is there anyway to modify this to look at a different date versus Today? Such as a custom date field?

Any help would be greatly appreciated!


Is there a formula to display the calendar week
I want to display the following for the 2nd week of FEB,2013
eg: 02/10/2013 to 02/16/2013
and for 3rd as :
02/17/2013 to 02/23/2013

I want to display the date range, not week1 week2 format

Any pointers would be greatly appriciated, I want a formula not apex code thanks in advance 🙂

apex formula datetime calendar


TEXT(MONTH(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ ' to ' + 
TEXT(MONTH((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))

which on today’s date (02/14/2013), would return:

2/10/2013 to 2/17/2013


Answer

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

Attribution
Source : Link , Question Author : user4935 , Answer Author : Jesse Altman

Leave a Comment