2

I am trying to create a formula field that will return the Day of the Week concatenated with the date the record was created. The end result should look like this:

Tuesday - 8/13/2019

I have this so far which returns the day of the week the record was created, but I can't seem to concatenate to show just the date after it:

CASE( MOD( DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")

I tried the below also, but I get an error saying: Incorrect parameter type for operator '+'. Expected Text, received Date (Related field: Formula)

   CASE( MOD( DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error") + DATEVALUE(CreatedDate)
1

The formula you're looking for is this one. I took the liberty of simplifying the weekday part too.

CASE( WEEKDAY( DATEVALUE(CreatedDate)), 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday", 7, "Saturday","Error") & ' - ' & TEXT(DATEVALUE(CreatedDate))

0

Actually, this should be a separate answer:

CASE( WEEKDAY( DATEVALUE(CreatedDate)), 
    1, "Sunday", 
    2, "Monday", 
    3, "Tuesday", 
    4, "Wednesday", 
    5, "Thursday", 
    6, "Friday", 
    7, "Saturday",
    "Error") & ' - ' & 
LPAD(TEXT(MONTH(DATEVALUE(CreatedDate))),2,"0") & "/" & 
LPAD(TEXT(DAY(DATEVALUE(CreatedDate))),2,"0") & "/" & 
TEXT(YEAR(DATEVALUE(CreatedDate)))

There's no locale-aware way of converting a date to text, so the TEXT(date) function always returns the unambiguous ISO format. If you want to use another format, you have to build it yourself. Here we take each component of the date (which has to be converted from datetime by DATEVALUE()), and convert that integer to text, concatenating in the slashes. The LPAD(n,2,"0") pads on the left with a zero if the string is less than two characters, ie, adds leading zeroes for days & months < 10.

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.