+ Start a Discussion
sales force 48sales force 48 

getting date for selected day as per requirement

Hi developers

for opportunity object
i have a custom field (picklist) with all week days likely {monday,tuesday,wednesday,thursday,friday,saturday}


i need a formulae field (code) 
intially when record is created i want to get the date for that created date
again when i update the existing record and selected some weekday from the customfield (picklist) so the formulae field i should get the corresding weekday date 

Jason Curtis NBSFDGJason Curtis NBSFDG
Hi, so basically you want a formula that if your custom picklist field is null, then you use the created date, but if there is a value (monday, tuesday, etc) in the picklist field you use a date based on that value. What I don't understand is which weekday DATE do you want based on the picklist value. I.e. if Monday was choose in the picklist, what date would you expect the formula field be populated with?
sales force 48sales force 48
1)when a record is inserted the created date have to be shown in the formulae field ( let say created date is 11/09/2014 updated in formule field )
2)when i update the inserted record and selected a day from the picklist (let say monday) and click save, so the formulae field to be updated with date 11/10.2014 (becoz comming monday is 11/10/2014) and if i select tuesday from picklist the formule field to be updated with 11/12/2014 as 11/12/2014 is tuesday
Jason Curtis NBSFDGJason Curtis NBSFDG
There is some good advice on adding values to a date to find the next day of the week here: 
https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#getting-next-DoW-after-date (https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#getting-next-DoW-after-date)
Finding the Next Day of the Week After a Date
To find the date of the next occurrence of a particular day of the week following a given Date, get the difference in the number of days of the week between a date and a day_of_week, a number 0–6 where 0 = Sunday and 6 = Saturday. By adding this difference to the current date, you’ll find the date of the day_of_week. The IF() statement in this formula handles cases where the day_of_week is prior to the day of the week of the date value (e.g. date is a Thursday and day_of_week is a Monday) by adding 7 to the difference.
date + ( day_of_week - MOD( date - DATE( 1900, 1, 7 ), 7 ) )
  MOD( date - DATE( 1900, 1, 7 ), 7 ) >= day_of_week,
You can substitute either a constant or another field in for the day_of_week value based on your needs.

The one thing you'll need to do is convert that picklist value to a number value. You can do that in the formula or you could create another field that does it separately.