Calculating number of months between two dates in Salesforce

Standard

It has been a long time since I blogged about a formula. Not that I haven’t built any, just nothing really new and exciting…until now that is!

excited

The problem under consideration is calculating the number of months between two dates. Before you go all “Phhhh, that is cake” take another think on this! See, it is easy for a human. We know that there are 12 months in a year, but to get a machine to do this automagically, well, it is not so straight forward. Getting days is easy (Date2 – Date1) and getting years even wouldn’t be too bad (year(date2)-year(date1)) but month is kind of an oddity, because you can’t just say Month(date2)-Month(date1) because the month is just this lonely little digit out there…you need the year to give context. But, how do you express that weirdness in a formula? Well, read on my friends.

My first instinct was to go with the number of days divided by 30, but about a nano second after that popped in my head, I remembered the whole “odd number of days in a month” thing. Not to mention, freaking leap years!

So, back to the drawing board I went…and I thought and I sketched some ideas out and finally, I hit upon the solution! I needed to not think about this as one problem, but three separate math problems.

To start with, here are my dates:

5/1/2015 and 6/23/2018

First thing, in the oldest date, we need to calculate the number of months left in that dates year…

Example – if the date is 5/1/2015, the value here would be 12 – 5 , which equals 7

Second, in the newest date, we need to calculate the number of months that have already passed…

Example – if the date is 6/23/2018, the value would be 6

Third, we figure out how many FULL years are between the newest year and next oldest year and we multiply that by 12. Since we have already considered the number of months for the oldest date, we need to add 1 to the year. We then subtract that number from the year value of the newest date.

Example – the next year for the oldest date is 2016. The newest year is 2018. The difference is 2 full years or (2*12) = 24.

Now, you sum all three values together like so:

7 (Number of Months Left in Oldest Date) + 6 (Number of Months that have passed in Newest Date)+(2*12(Number of Full Years times 12)) = 37 months.

Of course, the formula doesn’t actually look this nice…the formula itself is, well, awesomely gnarly!gnarly

Feel free to use, just replace “Your End Date” with whatever end date you want and “Your Start Date” with whatever start date you want!

/*checks first if the start / end dates are in the same year. If this is true, then the value should just be the month – month*/

if(year(Your_End_Date)-year(Your_Start_Date)=0,month(Your_End_date)-month(Your_Start_Date),

/*If this is not the case, are they at least 1 full calendar year apart? If this is the case, then do the big calc*/

if((year(Your_End_date)-(year(Your_Start_Date)+1))>=1,

/*Value if True*/

(

((year(Your_End_date))-(year(Your_Start_Date)+1))*12)+

((12-month(Your_Start_Date))+month(Your_End_date)),

/*If the years are not more than 1 apart, do this calc instead*/

(12-month(Your_Start_Date))+month(Your_End_date)))

An awesome variation of this is where you substitute “start date” with “today()”. This will give you a dynamic countdown of the months left between “today()” and whatever the end date is, as long as your start date is before “today”!!!

So, where would you use this? I built this out for a specific request, but I could imagine a couple other purposes:

  • Depreciation of a value
  • Territory Realignment that is weighted for time remaining in a date range
  • Reporting on the number of months between a created date and a closed date

Any other ideas? Thoughts?

Let me know!

andrew

Lego, Salesforce and a den of Tigers!

Standard

I am privileged to be an assistant den leader for my local Boy Scout Pack (shout out, Mt. Baker Council!) . My son is a Tiger Cub in Den 2 and I recently gave a talk to the den around the topic of communications.

Tiger Cub!

Too Much Fun!

I volunteered to talk because I have a means of communicating ideas to the masses, which you are reading at this time.

It was a load of fun and I was able to hold their interest for an impressive (IMHO) amount of time even though my blog is just 1’s and 0’s instead of a printing press or antenna.

At the conclusion of my talk, I asked the cubs to pick my next topic. The topics were…interesting, but we finally nailed it down to Lego (Followed by Minecraft! Shocking Topics, I know!).

Honestly, it didn’t make me too sad to talk about Lego because really, there are a few things that I can distinctly identify as being huge influence’s on my life and career, and Lego is one of them.

I had quite a few Lego sets growing up, though nowhere near as much as my kiddos do. I can still remember a few of them, and will sometimes take a trip down memory lane (AKA, Ebay) and lament over selling them. I had some of the original space sets and I can still remember how proud I was when I completed the space shuttle set.

Oh man, how awesome was this set?

That scaffolding almost killed me!

But how can Lego’s be connected to Salesforce? Well, glad you asked! Here are some of the lessons I have learned:

  • To get from Point A to Point B, just follow instructions.
  • Always be (preparing to) improve(ing)
  • Have Fun!

Alright then, EVERYTHING looks AWESOME so let’s expand on this!

  • To get from Point A to Point B, just follow instructions.

READ the MANUAL! Salesforce has some CRAZY good instructions and guides that are accessible in a variety of ways. I have found that as long as you read the given guides you should be OK!

  • Always be (preparing to) improve(ing)
000124.0013174.tif

You will STICK (ha) to your requirements docs!

Things are going to change! I am one of those types that don’t get bothered by my Lego sets breaking. In fact, I am usually thinking about the next thing I can build while I am building. Everything breaks…even if you do manage to kraggle

your sets together, you cannot stop time…eventually the plastic breaks down.

  • Have Fun

I love working in Salesforce, don’t you?

Oh Yeah!

Ready for Casual Friday!

WHAT CONDITION YOUR VALIDATION IS IN – Part 2

Standard

My recent post on bypassing validation rules (WHAT CONDITION YOUR VALIDATION IS IN) generated a record number of comments. It is all kinds of awesome to know that folks are reading the blog AND asking questions. whos-awesome

Two comments in particular were around pushing the userID over to the flow. After much facepalming, I realized that I never really explained how to do that.

Batman-Facepalm

There are actually a couple of ways of pushing data to a flow, however, I am going to share with you all my absolute favorite ways of doing this that you can also apply to all sort of other situations. In true “SFDCinSEA” fashion we are going to take the well documented idea of a pushing data in a URL (Best write up EVER!) , applying that notion to flows (From the Source!) and throwing in a bit of a curve ball by doing all this from a formula field (Evil laugh goes here!).

To do this, you will need the following:

  1. A flow
  2. A flow variable for the running user (varUserId)…What was actually asked about in the comments
  3. A flow variable for the accountid (VarPassedAccountID)…Just because I can
  4. A Hyperlink field

The basic premise is that we are going to use the hyperlink to launch the flow. When the user clicks on the hyperlink, it will pass over the accountID and running UserId.

Enough build up…here is what it looks like:

“Hyperlink(“/flow/validation_rule?varUserID=”&$User.Id&”&varPassedAccountID=”&Id,”Validation Rule Update”)”

No title can ever do the utility of this justice!

Sorry, you will need to click on it to see it in all of its weird font glory

Hmmm, that was really anticlimatic..wish there was a way to kick that up a notch, but there really isn’t…this is just a good use of the tools salesforce gives us!

Here it is sitting there looking awesome on the layout.

Yeah, that is pretty!

just want to smoosh it’s cute little URL

And here is proof that the values are passing!

bypass part two

MOAR BLURRING!

And yes, yes I am passing two values over to the flow.

Yes, yes I am

You can even use similar type of functionality if you decide to use a button.

One of the reasons I like doing this in a hyperlink field though is that I can change dynamically how this field is presented. For instance, I could use an image field to change how this looks based on some criteria. I could even “mask” the data by looking at the running user. Heck, I could even change which flow is actually running!

Enjoy, and keep the dialogue going!

What condition your validation is in

Standard

Validation rules and Decaf coffee are both things that can really mess up a morning.

what a crazy validation rule

one day, this will happen

Suppose you had a flow, and you were running said flow on an object that happened to have a validation rule right smack in the middle of the flow superhighway

The typical course of action is to write yet another exception to that validation rule based on profile, role, user, time of the day, etc..

Breaking the Rules

Breaking the Rules

The problem though is that if you create an exception to the validation to let your flow run, you have to let that exception go through the UI as well. Kind of like if your windows could only be opened all the way or closed tight…

Argh, if only there was a way around this…oh wait, the reason I am posting this is exactly because I do have a work around!

 

I prefer to think of it as Agile

I prefer to think of it as being Agile

We are going to do couple of tweeks to the flow and to the user record.

1) Add a new checkbox on the user record called “Ultra Awesome Validation Bypass”…or something like that.

Yeah totally just did that

Totally just happened

 

On the flow, we are going to add a couple things before the main updates…

1) Add a new element to the visual flow that does a look up on the user record

2) Add a new element to the visual flow that updates the field “Ultra Awesome Validation Bypass” to “TRUE”

3) Add a new element to the visual flow that updates the field “Ultra Awesome Validation Bypass” to “FALSE”

After

Start to Finish

 

Last Step (Pinky Promise!) is that we add in an exception to the workflow that if the “Ultra Awesome Validation Bypass” field for the running user is TRUE then the update is ignored.

We don't need your rules!

We don’t need your rules!

 

So, there ya have it. Now, like everything else, this is just meant to be a foundation for more and better and awesomer things. I personally really like this idea because it gives a flexible and situation driven method to bypassing validation rules.

Any questions or Comments? Send them on over! I love to hear from you all!

 

I got to talk at Dreamforce ’14!!!

Standard

Hey, Guess what?

I was invited to speak at Dreamforce 2014!

The topic I was asked to talk about is using flows for the good of the admin, not just the end user.

It was an AMAZING time and I LOVED speaking to a packed house!

2014-10-30 09_45_55-My name on the plasma 2 - Windows Photo Viewer

My name on the Plasma!

Time to get nervous!

Time to get nervous!

The Crowd!

Holy Smokes! That’s a lot of people!

So, the presentation went very well, despite Murphy wandering around an trying his best to muck things up!
Here are some lessons I learned:

1) Prepare, Prepare, Prepare…On the day of my talk, I had literally given my speech over a dozen times to various sized crowds.

2) Have multiple ways of displaying your presentation. I should have had my presentation on thumb drive, dvd, vhs etc…

3) Take more videos! I used a video demo of the functionality, but tried to do a live demo of the build components, which failed. I should have used a video of that as well.

4) Be prepared to just move along (especially if your instinct is to just fix things!). See #3 above…I had trouble getting the web up on the big screen. My first instinct was to try and fix it, but I had to keep pace of presentation up, so I skipped it. Good call!

Overall, this was one of the coolest things I have done professionally! It was EXTREMELY gratifying to see that something I had identified as problematic was shared by LOADS of you!

I have posted files of the screen caps to linkedin, success.salesforce.com and the dreamforce site!

Enjoy, and let me know if there are any questions.

How many days

What I learned about work during my long weekend

Standard

I have long advocated that when you have fun doing something for work that you need to be especially certain to take some time away from the keyboard to recharge. I like to spend time with the family, tinker in my garage, read and play video games. This last weekend was a long weekend (for me) and I think I hit all of these points. The neat thing about “away from keyboard” time is that typically, I end up getting some sort of neat idea or having a profound thought or two, which I think I will now share.

1) Changing the river starts small.

 Not the exact spot, but pretty close. Yes, I am lucky to live here.

We have had a stretch of phenomenal weather up here in the great PNW, so we went to the river on Monday. After doing the usual bit of exploring and wading, me and the kiddos started doing what we usually do…building. We built up pools in the shallows with rocks and ran some experiments on how the river behavior changes just by us moving things around. It was really neat to take in the fact that yes, we were changing things and no, we probably wouldn’t see the results for a very long time.

2) Fixing one thing might expose other borked areas.

Borked it again

I spent some time fixing my radio control airplane on Friday. It was a lot of fun, and I was able to get the servo sort of fixed. Excited for a test flight, I fired up the motor and gave it a toss just to witness it come crashing down. Turns out, I fixed the servo, but messed up the horizontal flap alignments.

3) Know when to walk away.

It's in your head now

Being the fixer person that I am, I did not take point #2 easily. However, it was getting late, I was tired and my brain was getting fuzzy. I have been dealing with the cruel mistress R/C for long enough to know that being tired with a fuzzy brain around xacto knives and duct tape can lead to some bad decisions so I walked away. In fact, I didn’t touch the plane again all weekend.

4) Don’t rush to build.

Not this type of rush

I have a R/C airboat that has a problem that I created. I put a pretty powerful motor / propellor combination on it. In fact, the propellor creates so much thrust that the boat will not turn straight. I started working on a solution but realized that I needed a method of testing that didn’t involve a lake and wading. So, exercising point #3, I walked away and just chewed on the problem for a while. I don’t remeber exactly when, but some time later on in the day, the solution came to me. Instead of reaching for pen and paper to sketch out the design, or even just heading to the workbench, I pondered and refined in my head. Doing this allowed me to refine out quite a bit of the design before I even commited it to pen and paper.

How do you recharge? Do you find yourself saying, “I spend my free time <<work related activity goes here>>”? Take some time to just do this weekend and see what comes up.

 

andrew

 

 

 

Favorite Features in Summer ’14 that haven’t been mentioned yet

Standard

Three times per year, Salesforce Santa visits with big PDF full of goodies for all of us admins and dev’s. A lot of the attention has been on the GA of flow triggers, as it rightly should because that is awesome, so I decied to talk about some features that are more admin / end user centric that seemed to have flown under the radar so far.

If you haven’t yet, you can get your Summer Release Notes here.

1)      Access Publisher Actions without Chatter Enabled (Page 183)

Sure, this is only for mobile only, Not just for mobile (Thanks Eanna Cunnane!), use these suckers everywhere! because in SFDC1 you can launch actions via the “+” icon!

Things to watch out for: The location of Global Actions has been changed.

2)      Edit Profile Details from Mobile (Page 43)

Very, Very nice and a long time coming. If you can approve stuff via the mobile, you should be able to change your profile information.

Things to watch out for: Only the user’s photo can be edited in the iOS app…wait, what?

3)      Log mobile calendar events in Salesforce using Today (Page 48)

This is going to be a huge timesaver for road warriors. I also think you will get some great adoption rates along with better data since data can now be captured at the moment rather than hours or days later.

Things to watch out for: User logging “Dentist Appointments” and other personal meetings

4)      Ask Questions in the Feed with Chatter Questions (Beta) (Page 82)

More engagement = Better Business. Even if you just us this for internal questions and answers it will boost adoption and increase engagement and good vibes.

Things to watch out for: It is just in Beta for the time being

5)      Give different types of rewards (Page 133)

Personally, I would use this by giving each individual a certain amount of reward funding that they can give out at their discretion. If you allow for immediate and tangible recognition, morale will be improved.

Things to watch out for: What is the reporting like? Can you create workflows?

6)      Keep favorite folders in view (Page 169)

Oh man, this is amazing. My org has a bajillion folders and it is a pain to manage.

Things to watch out for: “Hey, where did my folder go?” will be the highest volume request for a while.

7)      Expanded Approval History Reports (Page 190)

This is something that is sorely needed by both the business and technical teams. It is pretty cumbersome as of now to report on things like elapsed times.

Things to watch out for: Requests to make approvals more efficient (“Can you make an alert that goes out to PersonX every hour after they get a request”).

 

Anything I missed? Anything I am off the mark on? Don’t worry, still polishing post 2 of 3 on cleaner page layouts.

 

Andrew

Reducing the amount of email while sending email

Standard

In my post titled “Cleaning the data that matters – and not all data matters” I finished the post up with the following:

“PS – For bonus points, create a nice email alert telling the reps their data is bad, and make it so it sends them that notice every time they edit the account OR opportunity…just put on a timer so it only sends once per day!”

To which, JaneIsaac replied:

“nice detective work. Could you share What the timer formula look like?”

Well JaneIsaac, this post if for you!

Not that type of request

I take requests…just not freebird!

The scenariois that we wanted to send out alerts if an account scored low data grade points and that account OR an opportunity related to that account was updated. After some quick research I saw that the updates were clustered, often receiving multiple updates in a short period of time. I didn’t want the alerts constantly kicking out.

So I built out a function so that prevent multiple alerts from being sent out in a given set of time. My functionality treats account and opportunity updates as two different actions, so I broke them out on this blog as such. Listing out the ingredients below and I will dissect the basic functionality after.

Must be about snack time...

Ingredients for tasty food, not tasty Workflows

For the Account alerts:

1 – Date field on Account “Data Alert Sent Date”

1 – Workflow rule for the Account Object “Data Grade Alert”:

Evaluation Criteria = “Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria”

Rule Criteria =

Account WorkFlow Criteria

AND(
AND(Account_Data_Grade__c <>”Acceptable”,Account_Data_Grade__c <>”Excellent”),
Open_Pipeline__c < 1,
AND(LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “, LastModifiedBy.ProfileId <>”xxxxxxxxxxxxxxx”),
(DATEVALUE(CreatedDate)<>Today()),
OR(ISBLANK(Data_Alert_Sent_Date__c),(Data_Alert_Sent_Date__c) <> today()))

2 – Immediate Workflow Actions

All about that (Account Workflow) action Boss

1 – Workflow email alert Sendemail to “Last Modified By”

Account Workflow Alert

       1 – Field Update “Data Alert Sent Date” with Today()

Account Field Update

 For the Opportunity alerts:

1 – Date field on Opportunity “Data Alert Sent Date”

1 – Workflow rule for the Opportunity Object “Data Grade Alert”:

Evaluation Criteria = “Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria”

Rule Criteria =

Opportunity Workflow Criteria

AND(
AND(Account.Account_Data_Grade__c <>”Acceptable”,Account.Account_Data_Grade__c <>”Excellent”),
AND(LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “, LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “),
Record_Type__c =”Open Opportunity”,
(DATEVALUE( Account.CreatedDate )<>Today()),
or(ISBLANK( Data_Alert_Sent_Date__c ), (Data_Alert_Sent_Date__c)<>TODAY()))

2 – Immediate Workflow Actions

All about that (Opportunity Workflow) action Boss

1 – Workflow email alert Send email to “Last Modified By”

Opportunity Email Alert

          1 – Field Update = “Data Alert Sent Date” with Today()

Opportunity Field Update

Taking a look at the mechanics:

I wish my cube was this cool...or that I had a flying monkey!

I wish my cube was this cool…or that I had a flying monkey!

The rule criteria’s are similar enough that we won’t have to dissect them both and since the interest is in the timer components, that is what I am going to focus on:

1)      (DATEVALUE( Account.CreatedDate )<>Today()) –  Ignore if the account is newly created

2)      But, the following situations are OK:

  1. (ISBLANK( Data_Alert_Sent_Date__c ) – The data alert sent date is Null (Never triggered before)
  2. (Data_Alert_Sent_Date__c)<>TODAY() – The data alert sent date does not equal Today()

This last line is what ensures that an alert will only send once per day. If the rule runs, (Data Quality = Poor and “Data Alert Sent Date” <>Today()), then the email alert gets sent out and the “Data Alert Sent Date” gets updated with the current day. If that record was updated ANY OTHER TIME during that day, the rule will not fire. I know I say this all the time, but what I really (Really) like about salesforce is that when it comes down to it, you can do some crazy cool stuff with zero code.

Clicks Not Code!

In other CRM’s, the above functionality takes 6 weeks and 2 developers.

 

Looking at this functionality now, I think a couple neat additions would have been:

1)      A rollup summary on opportunity.Data_Alert_Sent_Date__c (MAX), this way, you could have the account rule also looking at the last time an alert was sent out on ANY opportunity.

2)      A counter field update on the opportunity rule with a corresponding rollup on accounts. This would allow for reporting on ignored updates and thresh holding of the alerts.

But, the fun with Salesforce is the ability to rapidly prototype and tinker, so if I wanted to add in some new stuff, it is easy – peasy – lemon squeezy.

Well, hope you enjoyed this. I certainly had fun taking a look at something that was built out quite some time ago but continues to keep ticking! If there are any special requests, just let me know!