RSS

Tag Archives: Formula

Regex in Salesforce

Regex in Salesforce

(Pardon the dust. ūüôā This post is still a work in progress!)

Regex is a confusing language to me. But even though I find it confusing, I recognize it’s power to verify input. Here are my (ongoing) notes on Regex.


What is Regex?

Regex is a sequence of characters that defines a string pattern. Huh? Let’s break that down:

  • Regex is short for “regular expression”
  • A “regular expression” is a sequence of characters
  • That sequence of characters may look more like hieroglyphics than anything else
    • regex
  • Those hieroglyphics define a string pattern
  • A string pattern is a string of characters, such as a username or an email address

So Regex is hieroglyphics that define a string. ūüėÄ


Regex Names, Engines, and Flavors

Other Namesflavors

Regex is also known as Regexp.

Engines & Flavors

Regex is processed by software called an Engine. And to complicate things, there are lots of different engines, each with its own unique way of interpreting the regular expression. The syntax and behavior of a particular engine is called a regular expression flavor. What this means is that if you’re using Regex in Java you’ll be using one flavor, and if you’re using Regex in Python you’ll be using another flavor.

What Flavor Does Salesforce Use?

Salesforce uses the Java flavor (see Java.Util.Regex Class Pattern).


Where Is Regex Used In Salesforce

Salesforce has a REGEX function that is available in field validation formulas, Visual Flow, and Apex. The REGEX function is not available in formula fields or in Visualforce. Boo.


The Salesforce Regex Function

The Salesforce Regex Function looks like this:

REGEX(the_string_to_check, the_regular_expression)


Using REGEX In Validation Rules

Validation rules look for errors. If an error is found (the validation rules evaluates to True), an error message is displayed and the user must correct their input before the record can be saved.

This means that when you’re writing REGEX that matches what you want in the field you’ll need to negate the final result like this:

NOT(¬†REGEX(Zip__c, “[0-9]{5}(-[0-9]{4})?”)¬†)


Getting To The Nitty-Gritty Of Understanding Regex

nitty grittyOkay, so how do you start to understand Regex gobbly-gook? You take it just one little bite at a time!

So here we go!

gobbledygookHow characters are represented in Regex

Other than special characters, what you see is what the Regex formula is looking for. In this example the formula is looking for the letter “u” and only the letter “u”. If anything else is present the formula will evaluate to false:

REGEX(String, “u”)

A set of characters, or Character Class, is a group of characters between brackets. Character classes allow for several different characters to be present at a given location in the string. In this example the formula is looking for a single character, the letter “a”, the letter “f”, or the digit “2”:

REGEX(String, “[af2]”)

To include all the characters between two given characters you can use a dash. In this example the formula is looking for a single character between “t” and “x” or between “R” and “Y”:

REGEX(String, “[t-xR-Y]”)

Negation

To exclude characters you put a carat (^) at the beginning of the character class. In this example the formula is looking for a single character other than “g”, “e”, or “n”:

REGEX(String, “[^gen]”)

Note: the carat is a special character that has dual meaning. When it is used within a character class it means negation. When it it used outside of a character class it signifies the beginning of the string (see further down in the post for this).


Grouping

grouping

Characters can be grouped together by including them between parentheses. In this example the string “bouncy ” is grouped and the entire function matches the string “red bouncy ball”:

REGEX(String, “red (bouncy )ball”)

 


Wildcard Characters and Repeating Pattern Quantifiers

Wildcard-80lv

When you need to match any character or a given token zero or more times you can use wildcard characters and repeating patterns:

  • . (period) – Match any character except a line terminator
  • ? (question mark) – Match the preceding token 0 or 1 times
  • * (asterisk) – Match the preceding token 0 or more times
  • + (plus sign) – Match the preceding token 1 or more times
  • {n} – Match¬†the preceding token n times
  • {n,} – Match the preceding token n or more times
    • Note: {0,} is equivalent to * and {1,} is equivalent to +
  • {n,m} – Match the preceding token at least n times and no more than m times
    • Note: {0,1} is equivalent to ?

Examples:

  • REGEX(String, “pe.k”) will match “peak”, “peek”, “pe2k”, etc.
  • REGEX(String, “goat(ee)?”) will match “goat” and “goatee”
  • REGEX(String, “red (bouncy )?ball”) will match “red ball” and “red bouncy ball”
  • REGEX(String, “tiger9*”) will match “tiger”, “tiger9”, “tiger99”, “tiger999”, etc.
  • REGEX(String, “(lily)+”) will match “lily”, “lilylily”, “lilylilylily”, etc.
  • REGEX(String, “(boo){2}”) will match “booboo”
  • REGEX(String, “7{2}”) will match “77”, “777”, “7777”, etc.
  • REGEX(String, “xo{3,4}”) will match “xoxoxo” and “xoxoxoxo”

“Greedy,” “Lazy,” and “Possessive” Quantifiers

When an engine parses a string with quantifiers (things that multiply characters) there are ways to help the engine be more efficient. This is where “greedy,” “lazy,” and “possessive” quantifiers come. While it’s mostly not necessary to know the difference, it is helpful when reading someone else’s Regex to know what these things mean.¬† See this site for an excellent explanation of quantifiers.

Greedy Quantifiers

The special characters ?, *, +, {n}, {n,}, and {n,m} are called “greedy quantifiers” because they match as many characters as possible.


Lazy Quantifiers

Quantifiers combined with the question mark create what are called “Lazy Quantifiers.”

  • ?? – once or not at all
  • *? – zero or more times
  • +? – one or more times
  • {n}? – exactly n times
  • {n,}? – at least n times
  • {n,m}? – at least n times and up to m times

At first blush at appears that there isn’t any difference. The difference lies in how the engine parses the string to look for a match. With greedy quantifiers the engine will try to match as many instances of the quantified token as possible. With lazy quantifiers the engine will try to match as few as needed.


Possessive Quantifiers

Quantifiers combined with the plus sign create what are called “Possessive Quantifiers.”

  • ?+ = once or not at all
  • *+ – zero or more times
  • ++ – one or more times
  • {n}+ – exactly n times
  • {n,}+ – at least n times
  • {n,m}+ – at least n times and up to m times

These quantifiers are helpful when you want a match must fail quickly if it doesn’t follow a particular pattern. Using possessive quantifiers can increase the efficiency of your Regex function.


Special Characters and Metacharacters

SesameCharacters

Special characters are characters that have a special meaning to the regex engine. Metacharacters are special characters used within a character class.

Special Characters

These are the special characters in Regex:

  • \ (backslash) – is combined with another character to mean something else
  • ^ (carat) – 1) denotes the beginning of a line or 2) negates a character class
  • $ (dollar sign) – denotes the end of a line
  • . (dot or period) – represents any single character except a line terminator
  • | (pipe or vertical bar) – is used as an “OR” operator
  • ? (question mark) – multiplies a token 0 or 1 time
  • * (asterisk) – multiplies a token 0 or more times
  • + (plus sign) – multiplies a token 1 or more times
  • – (dash or hyphen) – used for indicating a sequence (range) of characters
  • () (parentheses) – enclose character groups
  • [ (open bracket) – enclose character classes
  • { (open curly brace) – used when multiplying a token a specified number of times

Metacharacters

Any of the special characters except these, the metacharacters, can be used within a character class (a group of characters within brackets) without escaping them. Metacharacters have special meaning within a character class depending on where they are used. If used in their special places, they have special meaning and you would need to escape them in order to test for the actual character. If used in their not-so-special places, they do not need to be escaped.

These are the metacharacters in Regex:

  • ] (closing bracket)
    • it’s special place is anywhere after the first position in the character class or after the negating carat
    • Example: “[]xyz]” tests for a closing bracket and the letters “x”, “y”, and “z”
      • This can also be written “[xyz\]]”
    • Example: “[^]867]” tests for anything that is not a closing bracket or the digits “8”, “6”, or “7”
    • Example: “[tip\]] tests for a closing bracket and the letters “t”, “i”, and “p”
      • This can also be written “[[tip]”
  • \ (backslash)
    • the backslash must always be escaped within the character class if it’s a character you’re testing for
    • Example: “[+-$*\\]” tests for +, -, $, *, and \
  • ^ (carat)
    • it’s special place is right after the opening bracket. Placed anywhere else it is just a character
    • Example: “[^pex]” – Here the carat means “not”. This would test for anything except the characters “p”, “e”, and “x”
    • Example: “[This^Too]” – Here the carat is nothing more than a character
  • – (dash or hyphen)
    • The hyphen has three special places:
      • right after the opening bracket
      • right before the closing bracket
      • right after the negating carat
    • used anywhere else it is just a character

Escape Character

The escape character is the backslash (\) character. This means to represent any special character in a string you would need to put the backslash in front of it like this:

  • \\\\ – the backslash character
  • \^ – the carat character
  • \$ – the dollar sign character
  • \. – the period character

Backslash – Character Combinations

Certain characters combined with a backslash represent certain non-alphanumeric characters:

  • \\t – tab character
  • \\n – new line (linefeed) character (this is a line terminator character)
  • \\r – carriage return character (this is a line terminator character)
  • \\f – form feed character
  • \\a – alert (bell) character
  • \\e – escape character
  • \\cx – the control character corresponding to “x” (ctrl-B would be “\\cB”)

Predefined Character Classes

The following are predefined character classes you can use:

  • . (period) – matches any character except a line terminator
  • \\d – matches any digit (shorthand for “[0-9]”)
  • \\D – matches any non-digit (shorthand for “[^0-9]”)
  • \\s – a white space character (space, tab, new line, form feed, carriage return, and \\x0B)
  • \\w – a “word character” (shorthand for “[a-zA-z_0-9]”)
  • \\W a non-word character (shorthand for “[^a-zA-z_0-9]” or “[^\w]”)

Boundaries

fence

Boundaries are things like the beginning or end of a line or a word. These are the boundary characters you can use:

  • ^ – the beginning of a line
  • $ – the end of a line
  • \\b – a word boundary
  • \\B – a non-word boundary
  • \\A – the beginning of input
  • \\G – the end of the previous match
  • \\Z – the end of the input except for a final terminator
  • \\z – the end of the input

Some Sample Regex Formulas

  • California Drivers License
    • REGEX(Drivers_License__c, “([A-Z]\\d{7})?”)
    • Example matches:
      • C7768934
      • nothing (the ? means 0 or 1 item)
    • Checks for a capital letter followed by 7 digits
  • Credit card number
    • REGEX(Credit_Card__c, “(((\\d{4}-){3}\\d{4})|\\d{16})?”)
    • Example matches:
      • 1234-1234-1234-1234
      • 1234123412341234
      • nothing (a blank field)
  • Email address
    • email regex
  • Number between 100 and 99999
    • \\b[1-9][0-9]{2,4}\\b
  • US Phone Number
    • REGEX(Phone,¬†“((\\([2-9]\\d{2}\\) ?[2-9]\\d{2}-\\d{4})|(([2-9][0-9]{2}-){2}\\d{4})|(([2-9][0-9]{2}\.){2}\\d{4})|([2-9]\\d{2}){2}\\d{4})?”)
    • Example matches:
      • (223)456-7890
      • (223) 456-7890
      • 223-456-7890
      • 223.456.7890
      • 2234567890
      • nothing
    • Interesting note: the area code and “exchange” code (the first three digits after the are code) cannot start with 0 or 1 (these numbers are reserved for special purposes). All the other digits can be any number¬†from 0 to 9.
  • Social Security Number
    • REGEX(SSN__c, “((\\d{3}-\\d{2}-\\d{4})|\\d{9})?”)
    • Example matches:
      • 123-45-6789
      • 123456789
      • nothing
  • US Zip Code
    • REGEX(BillingPostalCode, “\\d{5}(-\\d{4})?”)
    • Example matches:
      • 95610
      • 84328-4484

Some Regex Resources

regex

  • Regular-Expressions.info¬†– This site is a good place to learn more about Regex. Their tutorial jumps around a little bit, but for the most part is laid out fairly linearly.
  • Java.Util.Regex Class Pattern¬†– This is the Regex pattern that Salesforce uses with the one exception that the backslash character must be escaped with a backslash since it’s a special character in Salesforce. This means that instead of writing \d to indicate a digit from 0 to 9 you would need to write \\d (the first backslash tells Salesforce to use the second one for just what it is, a backslash character and not a special character).
  • Regex101.com¬†– This site is great for testing your Regex. You enter the expression in the top space and in the Test String space you place some strings. The window to the right of these spaces will tell you how the expression worked.
  • Regexr.com¬†– This is another site for testing your Regex.
  • RegexLib.com¬†– Browse for useful expressions and test your own expressions.
  • Regex Quantifiers¬†– This site is an excellent tutorial on quantifiers and much, much more
Advertisements
 
1 Comment

Posted by on June 8, 2017 in Formulas, Visual Flow

 

Tags: ,

Saving Line Breaks to Text Fields

Saving Line Breaks to Text Fields

Here is a quick little tip on how to save two strings, with a line break between them, to a text area field when you’re in Visual Flow.


Using BR() Doesn’t Work

Salesforce text formula fields allow the use of a BR() function to force a line feed in the value of the field.

For instance, if I have a formula field of “Line 1” & BR() & “Line 2”¬†the output will be:

Line break formula field output

Result of BR()

.

Visual Flow uses most of the same functions that are available in formula fields so it would make sense that BR() could be used in a flow formula. And, indeed, if BR() is used in a formula the Flow will save just fine.

Unfortunately, though, you will not get the result you want:

BR_ENCODED Result

BR_ENCODED Result


The Workaround

The workaround requires that you take one extra step, and use a different function.

The Extra Step

The extra step is to create a Text Template as follows:

  1. Enter a text character or string (in this example I’m using “~”)
  2. Tap the Enter key to go to the next line
  3. Enter the same character or string
Line Break Text Template

Line Break Text Template

.

The Alternate Function

The function you’ll use instead of BR() is SUBSTITUTE(). The syntax of SUBSTITUTE is:

SUBSTITUTE( WorkingString, StringToLocate, SubstitueString )

  • The WorkingString is the string that the Substitute function is working on
  • The StringToLocate is what you’re looking for in the WorkingString
  • The SubstituteString is the value that will be place into the WorkingString in lieu of the StringToLocate

So in my example this would look like:

  • SUBSTITUTE(TextTemplate, “~”, “”)

The Final Formula

So your final formula will be:

String1 & SUBSTITUTE( TextTemplate, SearchString, “”) & String2

Line Break Formula

Line Break Formula


Using the Line Break Formula

You can now use your formula to populate Text Area fields that allow for separate lines. You can also use your formula to display text in display fields in your flow screens.


Taking it One Step Further – Just For Fun

Let’s take it one step further and add a line between strings. For example, let’s say you have a description field that you’re adding information to, but you want to see a visual break between the old and new text.

Description Field with Visual Break Between Comments

Description Field with Visual Break Between Comments

.

Super simple to do! Just add anything to your text template that you want to be added to the final value of your formula:

Text Template with Line

Text Template with Line


Resources:

 
1 Comment

Posted by on December 1, 2015 in Flow Tips & Tricks, Formulas, Visual Flow

 

Tags: , ,

Displaying Images Using Formula Fields

Displaying Images Using Formula Fields

 

A few months ago I wrote about displaying images in visual flow. Images can also be displayed on detail screens and list views through the use of formula fields. This is a totally cool way to bring a little life onto the screen and is fun to implement, to boot!

Example of image formula field on detail page:

Display Image Field on Detail Page

Image Field on Detail Page

.

Example of image formula field in related list:

Display Image Field in Related List

Image Field in Related List


First of All, Where can Images Be Stored?

Images can be stored in the same three places as with Visual Flow:

  1. In Documents
  2. In Static Resources
  3. On Salesforce Servers

See Displaying Images in Visual Flow for additional information on these options.


The Basic Image Formula

The basic formula to display an image is always the same:

IMAGE( “This is where the URL information goes“, “Alt text“, Height in pixels, Width in pixels )

Or a more precise example:¬†IMAGE( “/servlet/servlet.FileDownload?file=015F0000005tpB0”, “Wind Picture”, 45, 55)

Wind Picture

Wind Picture


Changing the Formula to Accommodate Where the Image is Stored

For accessing images stored in different places, the only difference in your formula will be the URL information, and, if stored as a Document, whether it is externally or internally available.


For Images Stored in Documents

Images stored in Documents must be individually available. In other words, even though a zipped folder of images can be uploaded to Documents, you will not be able to access the images in the folder.

Documents are also marked as Internally or Externally available and this can make a difference in the URL information.

For Internally or Externally Available Documents – /servlet/servlet.FileDownload

IMAGE( “/servlet/servlet.FileDownload?file=015F0000005tpB0″, “AltText”, Height, Width )

Example Formula using FileDownload

Example Formula using FileDownload

This is really your go-to formula when the image you want to display lives in Documents. Not only will it work for either Internally or Externally marked documents, it also requires only the Id of the document.

For Externally Available Documents – /servlet/servlet/ImageServer

IMAGE( “/servlet/servlet.ImageServer?id=015F0000005tpB0&oid=00DF000000066CS”, “AltText”, Height, Width )

Example Formula Using ImageServer

Example Formula Using ImageServer

Using ImageServer to access your images in Documents is a bit more complicated as you’ll also have to provide the organization Id (“oid”). Note also that the FileDownload parameter is a “file” and the ImageServer parameter is an “id”.

(To access the org Id go to Setup > Company Profile > Company Information and look for the “Organization ID” field.)


For Images Stored in Static Resources

Images stored in Static Resources can be individually available, however, they can also be grouped into a folder which is zipped and uploaded to Static Resources. This can be really handy for grouping images together that are used for similar purposes. And unlike Documents, Static Resources do not have an additional internally or externally available status.

Example Formula using Static Resources

Example Formula using Static Resources

In this example¬†the URL information is a reference to resource (“/resource/”) followed by the resource name (“GC_Images/”) followed by the file path (“GC_Images/Trees/”) followed by the full name of the image (“Tree03.jpg”).

It is important to note that if I had stored the image as a singular resource (not within a file folder tree) the URL would be simplified. In the above example the URL would become much simpler once the file path (“GC_Images/Trees/”) is eliminated:

IMAGE( “resource/GC_Images/Tree03.jpg”, “Inactive”, 25, 104)


How to Create Grouped Static Resource Images

This took me a while to figure out because I couldn’t find any explanations online that were dumbed-down enough for me to grasp. Then once I figured it out I discovered how easy it was…so it was no wonder I couldn’t find an explanation!

First of all, on your computer, organize your images in folders. You can place those folders inside of another folder if you want like I’ve done here, where all my images are in the folders that you see, and all these folders life in the “GC_Images” folder.

File Explorer View of File Structure

File Explorer View of File Structure

.

Next Zip your folder (for Windows, right click on main folder and select Send to > Compressed (zipped) folder).

Now go to Setup > Develop > Static Resources

Create a new Static Resource, if you haven’t done so already, or edit an existing one if you’re updating the images in your folders, and upload your zipped folder.

One warning when updating and existing resource: Be sure that any image names you have referenced are still present in the file structure. You can replace images, just be sure they’re named exactly the same as the image that is being referenced in your formula.

Static Resources View

Static Resources View

That’s all there is to it!


Sizing Your Images

Sizing your images can be a little tricky, and you may need to adjust the height and width several times before you get just the look you want. To cut down on the amount of time spent getting everything to look just right I do the following:

  1. I start by deciding what is the primary size factor I want to hone in on. If I’m going to be displaying two images side-by-side the height will be the most important factor. Vice versa, if the images are going to be one-atop-the-other, then width will be primary.
  2. I then fiddle with the image until I’ve got just the height or width I like. You can do this by naming the attributes in¬†your IMAGE formula and leaving off the size attribute you’re not worried about.
    1. For example, if I’m honing in on height: IMAGE( “/servlet/servlet.FileDownload?file=015F0000005tpB0″, alt=”AltText”, height=”64″¬†)
  3. Once I have the perfect height I make a note of it then turn to Excel to help me figure out what the ideal width would be. I enter the actual height and width of the image, and the desired height, and have Excel calculate the width.
    1. To get the actual height and width in Windows, right click on your image and select Properties then select the Details tab.
    2. Image Properties

      Image Properties

    3. Excel Formula to Convert Size

      Excel Formula to Convert Size

    4. Excel Formula to Convert Size

      Excel Formula to Convert Size

  4. For all the remaining¬†image I want to match to that height I’ll enter their actual height and width, and Excel will calculate the new width.
  5. Lastly, if the image is being stored in Documents I make a note of these items in the description of the image for future reference.
    1. Documenting Ideal Size in Description

      Documenting Ideal Size in Description


‘Hope this helps!

References:

 
Leave a comment

Posted by on November 19, 2015 in Formulas, Images

 

Tags: ,

Test For Presence of ID in Visual Flow

Test For Presence of ID in Visual Flow

Here is a quick question: What is the best way to test for the presence of an Id? For example, if you have a flow that may or may not receive an Id from the calling source, how do you know if you received on or not? Or, how do you know if the ParentId on an Account record (or AccountId on a Contact or Opportunity record) is populated or not?

The first thought that comes to mind is to see if the Id is an empty string:

Compare To Empty String

Compare To Empty String

Unfortunately this solution doesn’t always give you consistent results. ¬†Grrr. ūüė¶


So here’s a quickie tip:

Create a function that returns the length of the string, then check to see if the length is greater than zero:

Length Formula

Length Formula

Compare String Length to Zero

Compare String Length to Zero

Cheers!

 
1 Comment

Posted by on November 13, 2015 in Formulas, Visual Flow

 

Tags: , , ,

Number of Months Between Dates

Number of Months Between Dates

Have you ever wanted to calculate the number of months between two dates in a formula field or in a Flow? Happily, the solution is fairly straightforward.

Here are¬†a couple formulas you can use. In these examples I’m¬†using StartDate¬†as the older date and¬†EndDate¬†as the most recent date.

The basic formula:

IF(YEAR(EndDate) = YEAR(StartDate),
MONTH(EndDate) – MONTH(StartDate) + 1,
(YEAR(EndDate) – YEAR(StartDate) – 1) * 12
+ 12 – MONTH(StartDate) + 1
+ MONTH(EndDate))

Let’s test it out


First Test РDates in Same Year:

StartDate = 2/8/14
EndDate = 10/14/14

  1. YEAR(10/14/14) = YEAR(2/8/14) so the first part of the IF statement is evaluated
  2. MONTH(10/14/14) – MONTH(2/8/14) + 1 = 10 – 2 + 1 = 9

Second Test РDates in Adjacent Years:

StartDate = 2/8/14
EndDate = 10/14/15

  1. YEAR(10/14/15) <> YEAR(2/8/14) so the second part of the IF statement is evaluated
  2. YEAR(10/14/15) – YEAR(2/8/14) – 1 = 0
  3. 0 * 12 = 0
  4. 12 РMONTH(2/8/14)  + 1 = 12 Р2 + 1 = 11
  5. MONTH(10/14/15) = 10
  6. 0 + 11 + 10 = 21

Third Test РDates in Non-Adjacent Years:

StartDate = 2/8/13
EndDate = 10/14/15

  1. YEAR(10/14/15) <> YEAR(2/8/13) so the second part of the IF statement is evaluated
  2. YEAR(10/14/15) – YEAR(2/8/13) – 1 = 1
  3. 1 * 12 = 12
  4. 12 РMONTH(2/8/14)  + 1 = 12 Р2 + 1 = 11
  5. MONTH(10/14/15) = 10
  6. 12 + 11 + 10 = 33

Fourth Test РDates in Same Month and Year:

StartDate = 10/8/14
EndDate = 10/14/14

  1. YEAR(10/14/14) = YEAR(2/8/14) so the first part of the IF statement is evaluated
  2. MONTH(10/14/14) – MONTH(10/8/14) + 1 = 10 – 10 + 1 = 1

So far so good.

But what if you want to count only the full months that had passed?

For example, what if your StartDate was 2/20/14 and EndDate was 10/3/14 which means that 9 full months have not yet passed and you don’t want to count that last month until it has been fully realized? In this¬†example the basic formula would yield 9 months, but you want the outcome to be 8 months (at least until the EndDate is on or after the 20th of the month).

It turns out you’ll need to tweak the¬†basic formula twice–once to test separately for the dates being in the same month and year, and once to add¬†this statement:

– IF(DAY(EndDate) < DAY(StartDate), 1, 0)

The final formula will look like this:

IF(YEAR(EndDate) – YEAR(StartDate) + MONTH(EndDate) – MONTH(StartDate) = 0, 0,
IF(YEAR(EndDate) = YEAR(StartDate),
MONTH(EndDate) – MONTH(StartDate) + 1,
(YEAR(EndDate) – YEAR(StartDate) – 1) * 12
+ 12 – MONTH(StartDate) + 1
+ MONTH(EndDate)
)
– IF(DAY(EndDate) < DAY(StartDate), 1, 0)
)


Fifth Test РDates in Non-Adjacent Years Not Counting Incomplete Months:

StartDate = 2/28/13
EndDate = 10/4/15

  1. YEAR(10/14/15) – YEAR(2/28/13) + MONTH(10/14/15) – MONTH(2/28/13) <> 0 so the second part of the outer IF statement is evaluated
  2. YEAR(10/4/15) <> YEAR(2/28/13) so the second part of the inner IF statement is evaluated
  3. YEAR(10/4/15) – YEAR(2/28/13) – 1 = 1
  4. 1 * 12 = 12
  5. 12 РMONTH(2/28/14)  + 1 = 12 Р2 + 1 = 11
  6. MONTH(10/4/15) = 10
  7. 12 + 11 + 10 = 33
  8. DAY(10/4/15) < DAY(2/28/14) so IF statement will return 1
  9. 33 – 1 = 32

Sixth Test РDates in Non-Adjacent Years Not Counting Incomplete Months:

StartDate = 2/28/13
EndDate = 10/31/15

  1. YEAR(10/14/15) – YEAR(2/28/13) + MONTH(10/14/15) – MONTH(2/28/13) <> 0 so the second part of the outer IF statement is evaluated
  2. YEAR(10/31/15) <> YEAR(2/28/13) so the second part of the inner IF statement is evaluated
  3. YEAR(10/31/15) – YEAR(2/28/13) – 1 = 1
  4. 1 * 12 = 12
  5. 12 РMONTH(2/28/14)  + 1 = 12 Р2 + 1 = 11
  6. MONTH(10/31/15) = 10
  7. 12 + 11 + 10 = 33
  8. DAY(10/31/15) >= DAY(2/28/14) so IF statement will return 0
  9. 33 – 0 = 33

Last Test РDates in the Same Month and Year

StartDate = 10/7/15
EndDate = 10/28/15

  1. YEAR(10/28/15) РYEAR(10/7/15) + MONTH(10/28/15) РMONTH(10/7/15) = 0 so the outer IF statement returns 0

Cheers!

 
Leave a comment

Posted by on October 14, 2015 in Formulas

 

Tags: