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


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

    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))
| improve this answer | |
  • 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 '13 at 1:29
  • Quite right, it hadn't occurred to me that you could probably subtract it directly! – James Hill Feb 27 '13 at 1:57

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

| improve this answer | |

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.