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

2
  • 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, 2016 at 19:52
  • I'm not following. Can you explain more what you are asking?
    – cmmoutes13
    Aug 2, 2016 at 19:53

1 Answer 1

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
  • 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, 2016 at 19:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .