[SalesForce] Formula to get the start date of the week

Is there a way we can use a function like .tostartofweek() in formulas.

In short if the user enters date as 02/26/2013 i want the formula to get the entry_date.tostartofweek() in formula field

Any pointers would help

Best Answer

Nothing simple exists that I'm aware of.

However, check out the useful advanced formulae page: http://login.salesforce.com/help/doc/en/useful_advanced_formulas.htm

In particular, this has a formula for calculating the day of the week as a number (0 = Sunday...6=Sunday).

MOD(entry_date__c - DATE(1900, 1, 7), 7)

The above will give you Entry Date's day of the week. To get Sunday, you should be able to subtract the result of that from the Entry Date

DATE(
    YEAR(Entry_Date__c),
    Month(Entry_Date__c),
    DAY(Entry_Date__c)-MOD(entry_date__c - DATE(1900, 1, 7), 7))

Though you would need to add some complexity to this to handle the start of a month/year.

Edit:As @TomLogic points out below, you may be able to avoid using the DATE function and having to add complexity to handle start of month/year issues by subtracting the result of the Mod operation from the Date directly (though I've not yet tested it!)

Entry_Date__c - MOD(entry_date__c - DATE(1900, 1, 7), 7))
Related Topic