2

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

  • 1
    Shouldn't it be CASE(..., 6, 3, 0, 2, 1)? You can't use a day value of 0, you definitely need to adjust up by 1. – Adrian Larson Aug 2 '16 at 19:52
  • I'm not following. Can you explain more what you are asking? – cmmoutes13 Aug 2 '16 at 19:53
2

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.

  • 1
    Oh alright. I get what you are saying now. I just tried it and it seems to be working properly. Going to test a little bit more though. – cmmoutes13 Aug 2 '16 at 19:59

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.