I figured it out... I was SUPER surprised at how very difficult this task really is, especially when you start considering that some states have multiple time zones, and some don't observer daylight savings time, and that's not even considering any of the overseas countries!
I had this brilliant plan though. I built a Visualforce page, that simply pulled in an online time clock and replaced some of their parameters with the data from my Salesforce org, then dropped it into the account page layout and TADA! It's beautiful... and even ticks like a real clock and changes the seconds and everything!
After I did this in my sandbox (yet another reason to use the sandbox ALWAYS when testing) I discovered that a lot of the cities and states weren't programmed on that website/service. So, unfortunately that's not going to be my final iteration.
Yes, I've found quite a couple of apps on the appexchange that do this and Local Time v1.9 looks pretty good...
Go Local looks pretty good too... but it's $100 bucks a month for unlimited users!?!
I haven't found a way to pull VF components into a report... and I can't in good consciousness recommend a component that doesn't do everything we want that costs $1200 dollars a year. (we've got 350 standard users, 1503 portal users, and 29 partner users.)
So, now I'm going back to the drawing board. Just because this didn't meet my users requirements doesn't mean that someone out there might not want to use it. So here's the code!
I'm sure that there are other ways to do this... but here's the one I personally cobbled together.
<apex:page standardController="Account" renderAs="{!$CurrentPage.parameters.renderAs}">
<div style="position:relative;width:200px;">
<iframe src="http://www.thetimenow.com/clock/{!Account.Country_Full_Name__c}/{!Account.ShippingCity}/{!Account.State_Full_Name__c}?t=n
&embed=1&text=15&textdate=15&format=12&digitalclock=36&analogclock=60&letter_spacing=-2
&bordersize=1&bordercolor=BCE2F7&bgcolor=EBF8FF&colorloc=000000&colordigital=2C8EBF
&colordate=000000&styleloc=normal&styledigital=normal
&styledate=normal&right=0" frameborder="0" width="200" height="90" scrolling="no">
</iframe>
<a href="http://www.thetimenow.com/{!Account.Country_Full_Name__c}/{!Account.ShippingCity}/{!Account.State_Full_Name__c}"
style="position:absolute; top:0; left:0; display:inline-block; width:200px; height:90px; z-index:50;"
target="_blank"><span style="display:none;">{!Account.ShippingCity}, {!Account.State_Full_Name__c}, {!Account.Country_Full_Name__c}</span></a>
<div style="margin:0;padding:1px;font-size:11px;color:#666;text-align:center;">
© <a href="http://www.thetimenow.com" target="_blank" style="color:#666;text-decoration:none;">
The Time Now</a>
</div></div>
</apex:page>
To explain, I noticed that the specific time clock that I was using had full names for the country and the state, even though in my SFDC org, we were using the USPS abbreviations.
I created two formula fields, one for the Country Full Name, and one for State Full Name.
They look like this:
Country Full Name CASE( ShippingCountry , "US", "United States", "USA","United States", "CA", "Canada", "UK", "United Kingdom", "SZ", "Swaziland", "ET","Ethiopia", "MEX", "Mexico", "IS", "Iceland", "GU", "Guam", "GB", "Great Britain", "GR", "Greece", "IE", "Ireland", "DE", "Denmark", "FR", "France", "PE", "Peru", "CAN", "Canada", "JM", "Jamaica", "MISSING") State Full Name CASE( ShippingState , "AL","Alabama", "AK","Alaska", "AZ","Arizonan", "AR","Arkansas", "CA","California", "CO","Colorado", "CT","Connecticut", "DE","Delaware", "FL","Florida", "GA","Georgia", "HI","Hawaii", "ID","Idaho", "IL","Illinois", "IN","Indiana", "IA","Iowa", "KS","Kansas", "KY","Kentucky", "LA","Louisiana", "ME","Maine", "MD","Maryland", "MA","Massachusetts", "MI","Michigan", "MN","Minnesota", "MS","Mississippi", "MO","Missouri", "MT","Montana", "NE","Nebraska", "NV","Nevada", "NH","New Hampshire", "NJ","New Jersey", "NM","New Mexico", "NY","New York", "NC","North Carolina", "ND","North Dakota", "OH","Ohio", "OK","Oklahoma", "OR","Oregon", "PA","Pennsylvania", "RI","Rhode Island", "SC","South Carolina", "SD","South Dakota", "TN","Tennessee", "TX","Texas", "UT","Utah", "VT","Vermont", "VA","Virginia", "WA","Washington", "WV","West Virginia", "WI","Wisconsin", "WY","Wyoming", "MISSING")
You don't have to put them on any page layouts, but they do need to be visible to at least the system administrator profile.
Good Luck, and feel free to hit me up with questions, or give me some answers!
