5

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


1
2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.