+ Start a Discussion
BethC.ax700BethC.ax700 

Custom field for time stamping activity history when it's saved

Is it possible to create a formula that would include a time stamp whenever an an entry is logged in activity history? I want to be able to run a report and pull when calls are being made and emails are being sent.

 

Thanks for the help!!!!

Best Answer chosen by Admin (Salesforce Developers) 
Amber NeillAmber Neill

<blush> Awe shucks!

 

On a serious note, that formula only works for the Eastern time zone.  You can modify it (if needed) by using something other than 4/24 and 5/24 (that's 4 twenty-fourths and 5 twenty-fourths of a day).  So in the Central time zone they would be 5/24 and 6/24.

I hope this helps!
Good luck!
A,ber

All Answers

SteveMo__cSteveMo__c

You should be able to reference the Activity Created Date (Date/Time) in any standard Activity Report.  What exactly are you trying to do?

BethC.ax700BethC.ax700

Hi! Thanks for the response. I was told by sf.com support that this could not be done without building a custom field with a formula.

 

I'm trying to pull a report on activity history that shows when a call is logged and when an email is sent (time and date) for all sf.com users.

 

Is the activity create Date (Date/Time) you are referencing a custom field with a formula?

 

Thanks,

Beth

SteveMo__cSteveMo__c

Ugh!  you're right I just remembered another user running into this a while ago.  For some reason (don't ask me why) you cannot see the Time part of the Date/Time field Created Date when you run an Activity Report.  You're gonna need to create a custom Formula field that displays the full Date/Time value, and use that for your reports.  Let me dust off my notes on what I did for them, and I'll post my code. 

SteveMo__cSteveMo__c

Okay here's what you need to do.  In order to display the Time that a record was created you need to create a custom Formula(Text) field.  The formula will be:

 

TEXT(CreatedDate)

Now the problem with this is that the Time will be the for the GMT Time Zone (not the Local Time) for your organization.  In order to do that you would need to create another custom Formula(Text) field that evaluates the Created Date/Time and adjusts the Time to the Local TimeZone.  

 

MID(TEXT(CreatedDate - 0.1667), 12, 5)

 Now this one comes with it's own problems because it does not automatically adjust for Daylight Savings Time (are you ready to give up yet?).  Well don't!  because the lovely and talented Miss Amber Neill  

http://community.salesforce.com/t5/user/viewprofilepage/user-id/33261

has figured that out (when you speak of her, speak well).  

 

Here we go:
This formula field is called SetDateDST.  This is the formula:

IF(
(( setdate__c >= DATE(2003,4,6)) && (setdate__c <= DATE(2003,10,26))) ||
(( setdate__c >= DATE(2004,4,4)) && (setdate__c <= DATE(2004,10,31))) ||
(( setdate__c >= DATE(2005,4,3)) && (setdate__c <= DATE(2005,10,30))) ||
(( setdate__c >= DATE(2006,4,2)) && (setdate__c <= DATE(2006,10,29))) ||
(( setdate__c >= DATE(2007,3,11)) && (setdate__c <= DATE(2007,11,4))) ||
(( setdate__c >= DATE(2008,3,9)) && (setdate__c <= DATE(2008,11,2))) ||
(( setdate__c >= DATE(2009,3,8)) && (setdate__c <= DATE(2009,11,1))) ||
(( setdate__c >= DATE(2010,3,14)) && (setdate__c <= DATE(2010,11,7))) ||
(( setdate__c >= DATE(2011,3,13)) && (setdate__c <= DATE(2011,11,6))) ||
(( setdate__c >= DATE(2012,3,11)) && (setdate__c <= DATE(2012,11,4))) ||
(( setdate__c >= DATE(2013,3,10)) && (setdate__c <= DATE(2013,11,3))) ||
(( setdate__c >= DATE(2014,3,9)) && (setdate__c <= DATE(2014,11,2))) ||
(( setdate__c >= DATE(2015,3,8)) && (setdate__c <= DATE(2015,11,1)))
,(4/24),(5/24))

I use that field to calculate the hour of the SetDate.  Here's that formula field:

VALUE(MID (TEXT ( SetDateTime__c - SetDate_DST__c ), 12, 2))

As you can see I have two, very closely related, fields: SetDate and SetDateTime.  Both are in use for various reports depending on the level of granularity I need.

Let me know if this helps, hurts, or needs more explanation.

Thanks!

Amber

 

 

 

 

 

 

Amber NeillAmber Neill

<blush> Awe shucks!

 

On a serious note, that formula only works for the Eastern time zone.  You can modify it (if needed) by using something other than 4/24 and 5/24 (that's 4 twenty-fourths and 5 twenty-fourths of a day).  So in the Central time zone they would be 5/24 and 6/24.

I hope this helps!
Good luck!
A,ber

This was selected as the best answer
BethC.ax700BethC.ax700

thank you sooooooo very much!!!! :)

BenPBenP

I created 4 user fields.  Three to calculate the dst offset and one to show the local time.  The only issue is that I can't get local time for every state (the size gets too big).  I only need three for now, but looking forward I see a problem.

 

 

PST Offset:
IF(
(( TODAY() >= DATE(2010,3,14)) && (TODAY() <= DATE(2010,11,7))) ||
(( TODAY() >= DATE(2011,3,13)) && (TODAY() <= DATE(2011,11,6))) ||
(( TODAY() >= DATE(2012,3,11)) && (TODAY() <= DATE(2012,11,4))) ||
(( TODAY() >= DATE(2013,3,10)) && (TODAY() <= DATE(2013,11,3))) ||
(( TODAY() >= DATE(2014,3,9)) && (TODAY() <= DATE(2014,11,2))) ||
(( TODAY() >= DATE(2015,3,8)) && (TODAY() <= DATE(2015,11,1)))
,(7/24),(8/24))

Local Time:
CASE( State ,
"NC", MID(TEXT( NOW() - Time_Offset_EST__c ), 12, 5),
"WI", MID(TEXT( NOW() - Time_Offset_CST__c ), 12, 5),
"CA", MID(TEXT( NOW() - Time_Offset_PST__c ), 12, 5),
"Error")