Calendar Week Date Range as a formula field

Question

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 1

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

Answer 2

Try this, this is the corrected formula Formula return is Text For Saturday and Sunday - The range will be in the same week like if its 4/26 , it will show record in the range of 4/20 - 4/24

CASE(MONTH(DateValue(CreatedDate) - MOD(DateValue(CreatedDate) - DATE(1900,1,8),7)), 1, "January", 2, " February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", NULL)

+" "+

TEXT(DAY(DateValue(CreatedDate) - MOD(DateValue(CreatedDate) - DATE(1900,1,8),7)))

+" - "+

CASE(MONTH(CASE(CASE( MOD( DateValue(CreatedDate) - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","ERROR"), "Sunday", DateValue(CreatedDate) - 2, "Monday", DateValue(CreatedDate) + 4, "Tuesday", DateValue(CreatedDate) + 3, "Wednesday", DateValue(CreatedDate) + 2, "Thursday", DateValue(CreatedDate) + 1, "Friday", DateValue(CreatedDate), "Saturday", DateValue(CreatedDate)- 1, NULL)), 1, "January", 2, " February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", NULL)

+" "+

TEXT( DAY(CASE(MOD(DateValue(CreatedDate) - DATE(1900, 1, 6), 7), 0, DateValue(CreatedDate) - 1, 1, DateValue(CreatedDate) - 2, 2, DateValue(CreatedDate) + 4, 3, DateValue(CreatedDate) + 3, 4, DateValue(CreatedDate) + 2, 5, DateValue(CreatedDate) + 1, 6, DateValue(CreatedDate), TODAY())) )