You need to sign in to do that
Don't have an account?
Calculating Days Served per Year
Please help! I'm new at this. I'm trying to calculate the number of days served in the year. My data fields are date/time but I'm only wanting to calculate the days. I have some that came in the previous year (2011) but I only want to count the days in 2012. I have others that came in 2012 and are still there so I only want to count them through 12/31/2012.There is a total of 4 possibilities. Here is the formula that I have created but I keep getting Syntax errors. I'm trying to say if the Date Time In and Out fit the criteria then calculate it this way.
IF (DAY_ONLY() Date_Time_In__c < 2012-01-01 and Date_Time_Out__c <2012-12-31, Date_Time_Out__c - 2012-01-01 + 1,
(DAY_ONLY() Date_Time_In__c > 2012-01-01 and Date_Time_Out__c <2012-12-31, Date_Time_Out__c - Date_Time_In__c + 1,
(DAY_ONLY() Date_Time_In__c < 2012-01-01 and Date_Time_Out__c ISBLANK, 2012-12-31 2 - 012-01-01 + 1,
(DAY_ONLY() Date_Time_In__c > 2012-01-01 and Date_Time_Out__c ISBLANK, 2012-12-31 - Date_Time_In__c
))))
Is there an easier way to do this?
Thanks so much in advance for your help.
mjfroehlich
Wow. I deliberately wanted to avoid using a host of nested IF statements but thought never-the-less that this would be quite an easy formula to write. Instead it has captivated me as a puzzle for more than an hour—Salesforce’s treatment of DATETIME fields in formulas is quite restricting. This does what you need:
By the way, DAY_ONLY() is an APEX function. You can use it when writing classes/triggers/etc but not in formulas.
Good luck.
MellowRen
All Answers
mjfroehlich
Wow. I deliberately wanted to avoid using a host of nested IF statements but thought never-the-less that this would be quite an easy formula to write. Instead it has captivated me as a puzzle for more than an hour—Salesforce’s treatment of DATETIME fields in formulas is quite restricting. This does what you need:
By the way, DAY_ONLY() is an APEX function. You can use it when writing classes/triggers/etc but not in formulas.
Good luck.
MellowRen
MellowRen
Thank you so much for the time you spent on this. At first I did not think it was working but I changed the date to 2013 and it calculates perfectly for 2013. I am amazed! I still need to calculate it for the prior year (2012). I'm working on that, if you have any suggestions I would greatly appreciate it.
Thanks so much for what you have done.
Martha
Martha
Sorry I am confused. The formula I gave works perfectly for how I interpreted your question (which basically was “How many days did an employee work in 2012?”).
Hence by my understanding of the requirement:
What results are you expecting? Let me know and I'll help make it work.
Regards
MellowRen
MellowRen
I am so sorry. Your formula works GREAT!!! for both years! We have some problems on our end with data entry.
Thank you, Thank you, Thank you. We greatly appreciate your help with this.
Martha
Martha
Bad data, always a bane.
I am guessing you could set up some Validation rules to help prevent obvious mistakes in the future. Another idea is that you could wrap my formula in an IF statement with a condition looking for obvious errors (ie Date_Time_IN > Date_Time_OUT || ISBLANK(Date_Time_IN) etc) which if it is true you assign a negative number. Then by running a report with a filter on Days Served for a value less than 0 you could quickly find all the obvious mistakes.
Depends on how big your data set is and how often you are finding errors, if this would be worthwhile.
Anyway good luck. Glad I could help.
PS: I would love a Kudos :-)
Regards
MellowRen