2

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

2 Answers 2

5

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))
2
  • 3
    Instead of using the DATE() function, you could probably do entry_date__c - MOD(entry_date__c - DATE(1900, 1, 7), 7) to get to the Sunday of the week.
    – tomlogic
    Feb 27, 2013 at 1:29
  • Quite right, it hadn't occurred to me that you could probably subtract it directly!
    – James Hill
    Feb 27, 2013 at 1:57
4

You might be able to start with the answers to this question on how to determine the day of week for a given date. At that point, it should be easy to subtract days to get back to Sunday or Monday (depending on when you think a week begins).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .