Timesaver #8 – Calculating Business Days

 Salesforce, Time Savers  1 Response »
Nov 112011
 

Hey all,

So, on Twitter this week Jon Adams from Florida asked a question using the #askforce hash – “Is there a way to replicate the WORKDAY function in Excel in SFDC?”.  My first response was to send him as link to an answer on the Salesforce Community page which laid out how to calculate the number of business days between two date fields.  It’s a great formula to have in your back pocket, but it wasn’t exactly what he had in mind.  What if you had a Start Date, and knew that you wanted something to run for 26 business days which populated via a number field?

So, later that evening, I sat down to see what I could come up with.  The idea actually would save me a lot of time in the professional services field.  If someone from xLerate was going to be on a project for 13 days (business days), I wouldn’t have to play the calendar finger dance we do when we are attempting to figure this out.

To do this, I went on a custom object called Projects (you could easily use any native object you wanted as well…).  There is a field called Start Date, which I have used for the beginning date that we are attempting to calculate from.  There is also a field called “Number of Business Days” which has an API name of BizDays.  This is the number of business days you wish to count up to.

Next you will need to create a formula date field, which I have called End Date.  The formula would be as follows:

CASE(
MOD(Start_Date__c – DATE(1900, 1, 7), 7),
0, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c-1)/5)*2,
1, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c)/5)*2,
2, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c+1)/5)*2,
3, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c+2)/5)*2,
4, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c+3)/5)*2,
5, (Start_Date__c) + BizDays__c + CEILING((BizDays__c)/5)*2,
6, (Start_Date__c) – IF(BizDays__c>0,1,0) + BizDays__c + CEILING((BizDays__c)/5)*2,
null)

From this, the End Date will be calculated to the amount of business days (Mondays through to Fridays) stated in the field Business Days using the Start Date.

Fields from the formula

A caveat though – each country has separate and distinct holidays, so no holidays are added in this formula.  I am sure this could be calculated using a custom object and Apex, but for clicks not code administrators, this should assist.

How could I see this being used? Well, from a PS standpoint, it would be great for working out an end date of a project knowing the number of days that someone was to be working.  Or what if you wanted to know the date where you should be finishing an opportunity stage (above and beyond a Close date – say you wanted an opportunity to be closed if it took more than 30 business days to get to a 50% probability or above.

Hopefully you find this of assistance – if you think of other ways this could be use, let me know!

Know that I’m gonna be your dangerous side effect …

 Salesforce, Time Savers  4 Responses »
Nov 092011
 

So, this past week, salesforce.com released a new feature within their core application called “Social Contacts”.  Those who happened to attend or watch Dreamforce this year know that the Social Enterprise and the social contacts function got some major airplay during the Keynote speech by salesforce.com CEO Mark Benioff (if you watch again, you can actually see me three different times in the first part of the keynote – I know because my kids counted…).

What is Social Contacts? Social Contacts “lets you view your contacts’ social network profiles directly in Salesforce. This social information gives you a better perspective on your customers’ needs and issues—information you can use to build deeper relationships and increase business connections” (from salesforce.com help page).  In other words, when you turn this function on, you have quick and easy access to the public profile that your contact within Salesforce has allowed.  What networks are available?  To begin with, Salesforce has (for free) a Twitter and a Facebook link, and for a fee (this is actually LinkedIn’s AppExchange application and not an add-on fee from salesforce.com) you can utilize LinkedIn.

So, what happens after your administrator enables Social Contacts (for those who have an org that wasn’t created after October 7th, 2011)?  First, you will notice that your contact page changes slightly.  Up by the name is a new placeholder box for an image, much like we have become used to with Chatter (for those who don’t wish to add pictures in your org, I refer to you as the greyskins, in honour of Whitley Streiber).  We also have three social network icons of which we have all become accustomed to.
Clicking on these icons brings you to a tab page where you can choose the person’s Facebook or Twitter profile, and after this see their latest tweets, their public Facebook profile, and have a quick link to open their actual Twitter or Facebook pages in a new tab (please note: I have not paid for the LinkedIn app, so I cannot comment on this portion).  And, you can decide to add their Twitter or Facebook profile picture to their Salesforce contact.  And that’s it.  Yes, there is no integration, or pulling information (technically, Salesforce is using public API from Twitter and Facebook, so pulling information from these social networks would not be possible at this time, and to be honest, I think two way integration would end up costing), but it is a time saver.
How, you ask?  Well, consider these points:

  • Prior to Social Contacts, if I was on a contact’s detail page within Salesforce, and wondered what was up with them via Facebook or Twitter, I would have had to open a new window and go to Facebook, or flip over to my TweetDeck and search for the user (or open their URL).  Now, I have one-click access to their recent Twitter stream (which is how I find myself talking to people more and more) AND can reply, view their Facebook profile, and open with one click (and in a new window).  Now, I’m not sure about you all, but the moment I open either Facebook or Twitter, this massive, time sucking vortex is opened and the fifteen minutes I lost just moments ago can prove it.  This is a massive time saver for me, as I can check when a contact last tweeted, and send them a reply to their last tweet, or open straight to their Facebook and reply and write on their Wall (now salesforce.com needs to close my Facebook window once I click the Submit button! LOL).
  • I love the simple addition of the Facebook or Twitter profile picture.  I am a very visual person, and seeing something that reminds me of the actual contact (whether it be a nice picture like others have, or some weird kaleidoscope picture like I currently have) allows me to quickly remember who the person is I am currently viewing within Salesforce.
  • Especially with the Twitter aspect, being able to see and reply to a contact’s latest tweet, whether it be 10 seconds ago or 10 hours ago could be huge for someone maintaining a relationship with Salesforce contacts.  Let’s put aside the fact that we all work trying to be successful, whether it be selling widgets, services, knowledge or advice.  This is 2011, and the Mad Men ideal of a split between business and homestead is dead.   You follow these people on Twitter or Facebook, so unless you have a completely separate corporate account (an idea I am both am philosophically against, and loathe like the plague), your contacts know that outside of work you may have a life (in my case, I am sure that any business contacts or colleagues who follow me on Twitter know of my love of my family, hockey, live music, and my mistrust of the current Conservative government of Canada along with my love of all things Salesforce).  So, be a human.  Reply with a personal remark to their child’s picture, or their latest tech gadget, or their tweet of an article.  Make that conversation, strike up a personal report with the clients you deal with.  Personally, your circle of friends and acquaintances grows, and professionally, people are more likely to remember you because you’ve made that personal connection.
Is it perfect?  No.  And understandable so.  This is a first pass at this with Salesforce, and their utilization has been limited by the public API limitations of both Facebook and Twitter.  Do I foresee an evolution of this product?  Definitely.  It may come at a price to Salesforce users (lest we forget that Facebook and Twitter are companies, and are in the business to make money), but we don’t know that for sure.  However, given how salesforce.com has evolved products previously (do you remember the day when customization meant making a custom object, then we could use S-Controls, followed by Apex and Visualforce and now Heroku?), part of the application’s allure is seeing how the evolution of a feature will occur.  It truly is the dangerous side of Salesforce – waiting with baited breath for the next release and the newest updates!
So, have you turned on Social Contacts yet?  Let me know if you agree with me or not!
 Posted by at 1:48 pm

Hello out there, we’re on the air, it’s ‘Hockey Night’ tonight…

 Personal, Salesforce  No Responses »
Nov 042011
 

So, those who know me know that my love of what I do for a living is superseded likely only by my love of hockey (of course my wife and kids come first for everything….).  I played hockey for a very long time, until I couldn’t anymore, and now I spend a many a night listening and watching hockey, and spend a good part of the weekends coaching my son’s team.  And, yes, I am a Leafs fan living in Ottawa, but that’s for another post on another day…

This year (via Twitter) a bunch of us kool-aid kids of Salesforce decided that we should start a hockey pool.  Of course, this is typical for any Canadian hockey lover, but how would we manage it?  There are tonnes of cheap alternatives, but then I got to thinking – why not use Salesforce?  So, that is exactly what I did.  And believe it or not, it really only took a night of planning and a half a day of configuration to get the base done.  What did I do?

  • Created lead and account fields with each of the choices a person could make (we created a box pool, where you chose 18 skaters…15 forwards and 3 defencemen and 3 goaltenders)
  • Created a custom object called Players, which listed all the players, along with major stats (GP, G, A, Pts, W, L and GAA and Save Percentage….I should state I was a goalie so I like goalie stats!)
  • Created a custom object called Player Selections, which allow a team (placed in as an account) to be associated to their choices).  Cross-object formula fields allow me to pull the information from each player to each team that chose them.
  • A bunch of reports and dashboards to let people know where they stand on any given day.
  • Chatter used as a conversation piece (or to trash talk the living hell out of anyone cheering for a team that is not Blue and White in colour!  LOL)
So, why did I do this?  Many MANY apps exist on the net to do this, officepools.com, Yahoo sports, and ESPN/TSN are just three that popped in my head.  To be honest, I did it for a few reasons.  One – I live in salesforce.com’s applications and actually love what I do.  The idea of logging into a separate app in salesforce to update a hockey pool is not a bad idea for me – I’ve created little apps for my son to keep track of his beyblades, organize the hot lunch and milk programs at my kids’ school or manage a highland dance competition, so tracking a hockey pool wasn’t a far stretch.  Second, I hope that when 14 free seconds of time come up sometime between now and June (when hockey ends) I could use this to play and learn a bit more about Apex triggers and Visualforce.  Right now, that’s not happening (which is ok, as I manually update the stats every morning or second morning … I am a hockey fiend so the 15 minutes isn’t too bad), but I am hoping that soon I will be able to play more.
It’s been fun, and will keep you up to date with any progress – I’d like to add some triggers to automate score updating (right now I update all the scores, then use LexiLoader to update all the player selections to 0 so a formula runs), and start to create a VF page that is accessible online so people who are in the pool (and those just interested in seeing the ridiculousness of it all) have an easy place to go – right now we just log into the salesforce org, but it’d be great just to have an URL where you could quickly look at the dashboards, or how each team is doing! I think it’s a really cool showing of how CRM apps can help people out who really don’t have C’s (not your typical sales company) quickly and efficiently. You tell me though…
Everyone enjoy their weekend, and Go Leafs Go! Now, if you’ll excuse me, I have to go add in Kessel’s points to the CloudPool!
 Posted by at 8:35 am