I am currently trying to create a formula field that will display the first weekday of a month. So for instance 7/1/2016 was a Friday so the field would have displayed that date. But, if it would have been a Saturday then it should add two days to display the following Monday. Below is what I currently have, but it is returning #Error!
in the field for my test record and it should be showing 7/1/2016.
DATE(YEAR(Month_Start_Date_Copy__c), MONTH(Month_Start_Date_Copy__c),
CASE(MOD(DATE(YEAR(Month_Start_Date_Copy__c),MONTH(Month_Start_Date_Copy__c),1) - DATE(1900, 1, 7), 7),
0, 2,
1, 1,
0))
What I am trying to do here is have it return the day of the week, 0 being Saturday and then add 2 days to get to Monday and then if it is Sunday add 1 day to get to Monday. Anything else should just display the date that is in Month_Start_Date_Copy__c
Best Answer
The problem is that you should have:
DATE(<year>, <month>, 3)
DATE(<year>, <month>, 2)
DATE(<year>, <month>, 1)
If your
MOD
outputs0
forSunday
, it should yield6
forSaturday
. Hence this alternative seems like it should work:No valid date will ever have a day of 0, so your fallback value for the
CASE
statement is expected to yield an error.