0

I'm creating a formula field that identifies contacts with birthdays in the current week. I came up with this, but I'm getting this Syntax error:

Error: Incorrect argument type for function 'DATEVALUE()'.

Can anyone help

CASE(
    MOD((Birthdate) - DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())),7),
    0 , CASE( MOD(TODAY() - Birthdate, 7), 0 , 1, 0),
    1 , CASE( MOD(TODAY() - Birthdate, 7), 1 , 1, 0),
    2 , CASE( MOD(TODAY() - Birthdate, 7), 2 , 1, 0),
    3 , CASE( MOD(TODAY() - Birthdate, 7), 3 , 1, 0),
    4 , CASE( MOD(TODAY() - Birthdate, 7), 4 , 1, 0),
    5 , CASE( MOD(TODAY() - Birthdate, 7), 5 , 1, 0),
    6 , CASE( MOD(TODAY() - Birthdate, 7), 6 , 1, 0),
    0
)
2
  • Please edit your question to include your formula as text instead of as an image. Text is more accessible and makes it easier for people to adjust the formatting. Also, what is the field type for your Birthdate field? Is it missing a __c at the end?
    – Derek F
    Oct 15, 2023 at 20:35
  • Thank you. I will do that. The field type is Date and the field name is Birthdate. Oct 15, 2023 at 20:46

1 Answer 1

0

DATEVALUE(<some date field>) is wrong because DATEVALUE() expects either a datetime field, or a date-formatted string.

It's also unnecessary here because Birthdate is already a date (no need to convert something to a type if it's already the target type). Ok, some work here is necessary so you can get this year's birthdate for the comparisons. Point being, you should be using DATE(), passing this year, and then the month and date of the birthdate.

Syntax error aside, MOD() doesn't really seem to be the correct function to use here. The only time MOD(X - Y, Z) will equal MOD(Y - X, Z) is when X and Y are the same and/or when X and Y differ by Z (or if Z is 1). If the two dates are more than 7 days apart, then MOD() will force the result to still be between 0 and 6. If MOD(X - Y, 7) is 1, then MOD(Y - X, 7) will be 6.

For the most part, you probably just want some simple subtraction and a comparison based on the day of week of TODAY() (which is a prime use case for the WEEKDAY() function). In broad strokes, the logic you're looking for here is:

  • Assume that Sunday is the start of your week
  • Find the day of week of the Birthdate
    • Use that to get the Sunday (first day) of the birth week and the Saturday (last day) of the birth week
  • Do two comparisons with TODAY() to see if today lands within the date range determined above

A partial solution is

/* The "lower bound" check */
/* TODAY() must be greater than or equal to the Sunday of the birth week */
TODAY() 
>= 
DATE(YEAR(TODAY()), MONTH(Birthdate), DAY(Birthdate)) - CASE(
    /* WEEKDAY() returns 0-6, with 0 = Sunday */
    WEEKDAY(DATE(YEAR(TODAY()), MONTH(Birthdate), DAY(Birthdate))),
    /* Bday is on a Sunday, so we subtract 0 days */
    0, 0,
    /* Bday is on a Monday, so subtract 1 day to get to Sunday */
    1, 1,
    /* Bday is Tuesday, subtract 2 days to get to Sunday */
    2, 2,
    3, 3,
    4, 4,
    5, 5,
    6, 6, 
    0
)

And actually, since we're just returning the result of WEEKDAY() from the CASE(), we can do away with the CASE() entirely

/* lower bound check, today is at least Sunday of the birth week */
TODAY() >= DATE(YEAR(TODAY()), MONTH(Birthdate), DAY(Birthdate)) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(Birthdate), DAY(Birthdate)))

Since it's so easy for us to get to the Sunday of the birth week, we can do basically the same thing to get the last day (Saturday) of the birth week.

  • Get the Sunday of the birth week
  • Add 6 days to get to the Saturday of the birth week

Then you just need to say "today is less than or equal to the Saturday of the birth week". Since both conditions need to be true for today to be in the birth week, you'd AND() them together.

That is to say, TODAY() needs to be both >= the birth week's Sunday and <= the birth week's Saturday. If you used OR() here, then the formula would always return true.

4
  • Ok, thank you for your explanation. So what would be the final formula? Oct 16, 2023 at 12:22
  • @VaxyValmyr My intent here was to not provide you something that you could simply copy/paste. I'd like for you to give completing the formula a shot yourself. If there's a part of my explanation that isn't clear, let me know and I'll try to re-phrase it or include additional examples.
    – Derek F
    Oct 16, 2023 at 12:36
  • Ok, I appreciate that. I will work on that and let you know if I have questions. Thank you again Oct 16, 2023 at 13:07
  • I just found out that I can create a filter on the report that does this. Check out the filter for relative date options. then I used Operator 'Equals', Type 'Value', and Relative Date ‘NEXT 7 DAYs’. Thank you for your help! Oct 16, 2023 at 15:05

You must log in to answer this question.

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