1

I am curious if the following task can be done declaratively without Apex code. For our billing object we have the following fields:

payment_method   <- Picklist with various values.  I'm looking for (Credit|Debit)
card_expiration  <- String with the card expiration in the MM/YYYY format

I'm considering adding a new card_expiration_date field that uses a formula to check if the payment_method is Credit or Debit. If that is true then parse the card_expiration to yield the last day of the month in the year given.

The challenge here is to calculate the date accurately with regard to months ending with 28, 29, 30 or 31 days. If I want to pursue exact accuracy this could very well require me to dip in to Apex, which I'm actually more comfortable working in. However, the purpose for this is to identify whose cards are about to expire and email out a notification. We just need to get close. Since all months have 28 days, could this be done more simply with a formula field? In other words if I have the following data:

01/2016 --> formula yields 01/28/2016
02/2016 --> formula yields 02/28/2016
03/2016 --> formula yields 03/28/2016

What do you think?

1
  • you have FIND, MID, LEFT, RIGHT functions available to you in formula fields plus DATEVALUE to create dates from yyyy-mm-dd text format. Give it a try
    – cropredy
    Feb 3 '16 at 0:58
3

I was able to come up with a solution which I would not have been able to come up with had it not been for crop1645's comment. It even works with Leap Years. Here's my solution:

IF(
    payment_method = 'Credit Card' || payment_method = 'Debit Card', 
    IF(
        ISNUMBER(RIGHT(card_expiration,4)) && ISNUMBER(LEFT(card_expiration, 2)),
        IF(
            LEFT(card_expiration, 2) = '02',
            IF(
                OR(MOD(YEAR( DATEVALUE(RIGHT(card_expiration,4) + '-02-28') ), 400 ) = 0, AND(MOD( YEAR( DATEVALUE(RIGHT(card_expiration,4) + '-02-28') ), 4 ) = 0, MOD( YEAR( DATEVALUE(RIGHT(card_expiration,4) + '-02-28') ), 100 ) != 0)) = TRUE,
                DATEVALUE(RIGHT(card_expiration,4) + '-02-29'),
                DATEVALUE(RIGHT(card_expiration,4) + '-02-28')
            ),
            IF(
                VALUE(LEFT(card_expiration, 2)) <= 7,
                IF(
                    MOD(VALUE(LEFT(card_expiration, 2)),2) = 0,
                    DATEVALUE(RIGHT(card_expiration,4) + '-' + LEFT(card_expiration, 2) + '-30'),
                    DATEVALUE(RIGHT(card_expiration,4) + '-' + LEFT(card_expiration, 2) + '-31')
                ),
                IF(
                    MOD(VALUE(LEFT(card_expiration, 2)),2) = 0,
                    DATEVALUE(RIGHT(card_expiration,4) + '-' + LEFT(card_expiration, 2) + '-31'),
                    DATEVALUE(RIGHT(card_expiration,4) + '-' + LEFT(card_expiration, 2) + '-30')
                )
            )
        ), 
        null
    ), 
    null
)

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.