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
miss vmiss v 

Formula Not working - why?

Ok, I'm working with a custom formula to calculate the actual working days per month.  The key to everything is the Date Seen field ("Date_Seen__c")...  Everytime one of our counselors sees an employee, they populate the Date Seen field.  There is a formula that runs off of this field that basically calculates a "1" for every date seen IF( NOT(ISNULL(Date_Seen__c)) ,1, 0). 
 
That all works just fine.
 
Now, what is being asked of me is to calculate the number of employees see per working a day of each month.  For instance, if our counselor John Smith saw 100 employees in a month, and there were 20 working days per month, that would be an average of 5 per day.  So I told the system what number of working days to calcuate for each month with this formula:
 
IF(AND(MONTH(Date_Seen__c)=1,YEAR(Date_Seen__c)=2007 ),22,
IF(AND(MONTH(Date_Seen__c)=2,YEAR(Date_Seen__c)=2007 ),20,
IF(AND(MONTH(Date_Seen__c)=3,YEAR(Date_Seen__c)=2007 ),22,
IF(AND(MONTH(Date_Seen__c)=4,YEAR(Date_Seen__c)=2007 ),21,
IF(AND(MONTH(Date_Seen__c)=5,YEAR(Date_Seen__c)=2007 ),22,
IF(AND(MONTH(Date_Seen__c)=6,YEAR(Date_Seen__c)=2007 ),21,
IF(AND(MONTH(Date_Seen__c)=7,YEAR(Date_Seen__c)=2007 ),22,
IF(AND(MONTH(Date_Seen__c)=8,YEAR(Date_Seen__c)=2007 ),23,
IF(AND(MONTH(Date_Seen__c)=9,YEAR(Date_Seen__c)=2007 ),19,
IF(AND(MONTH(Date_Seen__c)=10,YEAR(Date_Seen__c)=2007 ),23,
IF(AND(MONTH(Date_Seen__c)=11,YEAR(Date_Seen__c)=2007 ),20,
IF(AND(MONTH(Date_Seen__c)=12,YEAR(Date_Seen__c)=2007 ),20, null
)
)
)
)
)
)
)
)
)
)
)
)
Then I ran a formula that says EE_SN__c / TT_Days_In_Month__c
 
I'm grouping the reports to group by month - but it doesn't calculate by the number I'm assigning.  For instance, in August, it's dividing by 25, and for June and July it's calculating by 20.  What am I doing wrong?
 
Any help would be really really appreciated.  :)
kevinedelmannkevinedelmann
It is a little hard to understand from what you wrote where the issue may be residing.

For instance, I am not sure where the Date Seen field resides.  Is it in a detail record, or a related list object related to an employee?  I need a little more background on this.

Another instance is in the long if/and formula it is in a custom formula field, correct?

Lastly, where was the EE_SN__c / TT_Days_In_Month__c formula run from?  The report or in another custom field?

Overall the logic makes sense, we just need to figure out where we need to tweak things.

Oh, I found something you may wish to use while looking through the formula field help section.  It is a custom formula that returns the Month based on a date field.  You could easily change that to show work days instead.  Also, you could then add to it a little easier when you need to have multiple years (as you know they will want this report next year too!  :smileyhappy:)

CASE( MONTH(Date_Seen__c) ,
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December",
"None")

Kevin
miss vmiss v

i appreciate your advice: 

All of the fields and formulas are in a custom object we call Benefit.   It is linked to another Custom Object, called Employee.   This includes the EE_SN_c/TT_Days_In_Month_c

I can try the formula you are suggesting, althought I'm a little confused on how that it would be different from the formula I already wrote for TT_Days_In_Month.  Can you explain that to me, or write a quick sample for one or two months?

 

Thanks.
kevinedelmannkevinedelmann
Let me know how it goes.  As for the other formula I showed you, it should work the same as yours, it is just a little cleaner and smaller and perhaps easier to update in the future.

Here is how I would set the formula up:

AND(Year(Date_Seen__c)=2007,

CASE( MONTH(Date_Seen__c) ,
1, 22,
2, 20,
3, 22,
4, 21,
5, 22,
6, 21,
7, 22,
8, 23,
9, 19,
10, 23,
11, 20,
12, 20,0)
)

Kevin
miss vmiss v
Hmm...  this still isn't calculating correctly.   The problem i think has to do with the summary of the EE_Seen.  When i use the reporting function to sum up the EE Seen, I'm grouping it by Month.  However, if I choose to "Show Details" in the report, it's cacluating the TT_Days_in_Month for each employee at .04 or .05 (depending on the month)...  I can't figure out if this is impacting the final number or not... 
just a thought?
kevinedelmannkevinedelmann
That may be the case.  Check the formula field to see if you are rounding to only 2 decimals.  You may need to extend it out to 4 or 5 places in order to get the accurate #.

Kevin
miss vmiss v

Simple enough - it worked!  Unfortunately, I've tried using the round formula so that it doesn't have to be 6 decimal points on my reports, but it always seems to round down. :( 

Either way, I'm just happy to have it calculating correctly!  Thanks for your help!