0

Currently I am using the below formula in my formula field to convert a date field to string :

IF(ISBLANK( Fiscal_Year End _c),
    NULL, 
    TEXT(DAY(Fiscal_Year End _c )) & ""& 
        CASE(
            MONTH( Fiscal_Year End _c ),
            1, "January", 
            2, "February",
            3, "March", 
            4, "April", 
            5, "May",
            6, "June", 
            7, "July", 
            8, "August", 
            9, "September",
            10, "October", 
            11, "November",
            12, "December", 
            "None" 
        )
)

Additionally, we would want the formula to convert the date field to string only if the Account type is Prospect or Customer. Any ideas how I can format the formula ?

Improve this question
2
  • 2
    Welcome to SFSE! Screenshots of code and formulas (and pictures taken of a screen) are highly discouraged. It's much, much better to copy and paste the code or formula and use the formatting tools (or markdown). Text is much more accessible and readable (when formatted properly). Please edit your question to include your formula as text instead of an image.
    – Derek F
    Commented Aug 10, 2022 at 12:03
  • Hey , @DerekF apologies for that and thanks for the heads up. I'm pretty new to the community but I appreciate your advice.
    – AlizehS
    Commented Aug 10, 2022 at 19:37

2 Answers 2

Reset to default
0

Your business requirements aren't 100% clear to me but this gives you the idea.

IF(OR(
      ISBLANK( Fiscal_Year_End__c)
      AND(NOT(ISPICKVAL(Type,'Prospect')),
          NOT(ISPICKVAL(Type,'Customer'))
      )
   ),
  NULL, /* no format if blank fiscal y/e or account type other than prospect/customer */
  TEXT(DAY(Fiscal_Year End _c )) & ""& 
    CASE(
        MONTH( Fiscal_Year End _c ),
        1, "January", 
        2, "February",
        3, "March", 
        4, "April", 
        5, "May",
        6, "June", 
        7, "July", 
        8, "August", 
        9, "September",
        10, "October", 
        11, "November",
        12, "December", 
        "None" 
    )
)
Improve this answer
6
  • To give you some context : I have a date field (Fiscal year end) and the formula field was initially created to convert the date field to string format. The current business requirement is such that the formula field should convert the date field to string only if the Account Type equals Prospect or Customer else it should display it in date format. The current formula does satisfy the condition of Account type but the latter part i.e if Account Type is anything other than Prospect or Customer it should display it in date format and not convert it to string is what I am looking for.
    – AlizehS
    Commented Aug 11, 2022 at 7:15
  • OK -- use edit and update your OP with these requirements and give a few examples of what you are expecting (inputs -> outputs).
    – cropredy
    Commented Aug 11, 2022 at 18:19
  • I think I just found what I was looking for. Below is the formula for the same : IF(ISBLANK( Fiscal_Year End__c), NULL, IF(OR(ISPICKVAL(Type,"Prospect"),ISPICKVAL(Type,"Customer")),TEXT(DAY(Fiscal_Year End _c )) & " " & CASE( MONTH( Fiscal_Year End _c ), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None" ),TEXT(Fiscal_Year End__c)))
    – AlizehS
    Commented Aug 12, 2022 at 12:22
  • This would convert the date field to string if and only if the account type = Prospect or Customer, else it would display the date field in the date format for anything other than Prospect or Customer.
    – AlizehS
    Commented Aug 12, 2022 at 12:29
  • post as an answer to benefit the community
    – cropredy
    Commented Aug 12, 2022 at 21:22
0

This would convert the date field to string if and only if the account type = Prospect or Customer, else it would display the date field in the date format for anything other than Prospect or Customer.

IF(ISBLANK( Fiscal_Year End__c), 
NULL, 
IF(OR(ISPICKVAL(Type,"Prospect"),ISPICKVAL(Type,"Customer")),
TEXT(DAY(Fiscal_Year End _c )) & " " & 
CASE( MONTH( Fiscal_Year End _c ), 
1, "January", 
2, "February", 
3, "March", 
4, "April", 
5, "May", 
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December", "None" ),TEXT(Fiscal_Year End__c)))
Improve this answer

You must log in to answer this question.

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