function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Patrick WaplesPatrick Waples 

Dashboard Quandry & a Small Formula Issue

I have a unique request from upper management for a dashboard.  They would like to track new unworked leads that come into the system in hourly buckets.

 

Essentially we would have a table on the dashboard that broke down like this:

 

New Leads:

 

1 hour      ||   30

2 hours    ||   30

3 hours    ||   30

4 hours    ||   30

5+ hours  ||   30

---------------------

Total Unworked Leads || 150

 

The problem is that while the creation date is a date/time field I can't seem to calculate based on the hour of creation...just the date.

 

Anyone have any ideas?

 

Finally...can anyone tell me why this formula:

 

CloseDate - DATEVALUE(Orig_Lead_Create_Date__c)

 

...would always give me a value of "0"?

 

In theory if I had a creation date of 03/18/08 11:15am and a close date of 06/30/08 then this formula should strip the timestamp off and just leave me with 06/30/08 - 03/18/08...which equals 104....but that's not what I'm coming back with :(

 

Appreciate the help!

 

P.

mh218mh218

 

Have you tried the following:

 

NOW() -  CreatedDate

 

 

However, the result of this formula is a number whose integer part is the number of days, and whose decimals are hours and minutes expressed as a fraction of 1 day.

For instance, it would return "13.5" for 13 days and 12 hours.

 

So you need to take the number of days and convert to number of hours:

 

( NOW() - CreatedDate ) * 24

 

This works with an assumption of a 24 hour day - if you only want it reflected in business hours then you are going to have to do some more calculations. 

 

Do you care about post lead conversion?  If so you might want to put an IF clause around the formula:

 

IF( 

IsConverted = False,

( NOW() - CreatedDate ) * 24 , 

(ConvertedDate - DATEVALUE( CreatedDate )) * 24

)

 

Except that isn't entirely accurate since ConvertedDate is a Date field and CreatedDate is a Datetime field.  By DATEVALUE( CreatedDate) you are converting the CreatedDate value to a Date and not a Datetime - which will skew the calculation somewhat on post-conversion reporting.  But if you aren't concerned with lead history reporting then you should be fine.  If you want to get the ConvertedDate to a Datetime value it will require some additional work.

 

As to your 2nd question, what value is returning in your example?  What type of field is Orig_Lead_Create_Date__c and how is it calculated?