[SalesForce] Formula to Find the First Weekday of a Given Month

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:

  • If the 1st is Saturday, the first weekday is on DATE(<year>, <month>, 3)
  • If the 1st is Sunday, the first weekday is on DATE(<year>, <month>, 2)
  • Otherwise the 1st is a weekday, so just use DATE(<year>, <month>, 1)

If your MOD outputs 0 for Sunday, it should yield 6 for Saturday. Hence this alternative seems like it should work:

DATE(YEAR(...), MONTH(...), CASE(
    MOD(...),
    6, 3,
    0, 2,
    1
))

No valid date will ever have a day of 0, so your fallback value for the CASE statement is expected to yield an error.

Related Topic